By: Sergey Gigoyan | Updated: 2021-12-08 | Comments (21) | Related: 1 | 2 | 3 | 4 | > TSQL
Problem
Sometimes it's necessary to find the maximum or minimum value from different columns in a table of the same data type. For example we have a table and three of its columns are of DATETIME type: UpdateByApp1Date, UpdateByApp2Date, UpdateByApp3Date. We want to retrieve data from the table and load it into another table, but we want to choose the maximum date from UpdateByApp1Date, UpdateByApp2Date, UpdateByApp3Date as the LastUpdateDate in the new table. SQL Server allows us to solve this task in different ways and in this tip we will illustrate these solutions and compare performance.
Solution
Let's assume that we have a sample table with five columns and three of them have a DATETIME data type. Data in this table is updated from three applications and the update data for each application is stored correspondingly in columns UpdateByApp1Date, UpdateByApp2Date, UpdateByApp3Date. Here is code to build the table and add some sample data.
IF (OBJECT_ID('tempdb..##TestTable') IS NOT NULL)
DROP TABLE ##TestTable
CREATE TABLE ##TestTable
(
ID INT IDENTITY(1,1) PRIMARY KEY,
Name NVARCHAR(40),
UpdateByApp1Date DATETIME,
UpdateByApp2Date DATETIME,
UpdateByApp3Date DATETIME
)
INSERT INTO ##TestTable(Name, UpdateByApp1Date, UpdateByApp2Date, UpdateByApp3Date )
VALUES('ABC', '2015-08-05','2015-08-04', '2015-08-06'),
('NewCopmany', '2014-07-05','2012-12-09', '2015-08-14'),
('MyCompany', '2015-03-05','2015-01-14', '2015-07-26')
SELECT * FROM ##TestTable
Our task is to find the last update date for each row using the three dates and we will provide four solutions for this task.
By looking at the above data this is the outcome we should expect:
Solution 1
The first solution is the following:
SELECT
ID,
(SELECT MAX(LastUpdateDate)
FROM (VALUES (UpdateByApp1Date),(UpdateByApp2Date),(UpdateByApp3Date)) AS UpdateDate(LastUpdateDate))
AS LastUpdateDate
FROM ##TestTable
Solution 2
We can accomplish this task by using UNPIVOT:
SELECT ID, MAX(UpdateDate) AS LastUpdateDate FROM ##TestTable UNPIVOT ( UpdateDate FOR DateVal IN ( UpdateByApp1Date, UpdateByApp2Date, UpdateByApp3Date ) ) AS u GROUP BY ID, Name
Solution 3
This task can be solved by using UNION:
SELECT ID, MAX(UpdateDate) AS LastUpdateDate FROM ( SELECT ID, UpdateByApp1Date AS UpdateDate FROM ##TestTable UNION SELECT ID, UpdateByApp2Date AS UpdateDate FROM ##TestTable UNION SELECT ID, UpdateByApp3Date AS UpdateDate FROM ##TestTable ) ud GROUP BY ID
Solution 4
And the fourth solution also uses a UNION:
SELECT ID,
( SELECT MAX(UpdateDate) AS LastUpdateDate
FROM
( SELECT tt.UpdateByApp1Date AS UpdateDate
UNION
SELECT tt.UpdateByApp2Date
UNION
SELECT tt.UpdateByApp3Date
) ud
) LastUpdateDate
FROM ##TestTable tt
Performance Comparison
As we can see the first two solutions have more compact code and therefore it is probably easier for developers to use, but what can be said about performance? Let's compare performance of these solutions.
Below we are creating a new table and adding a lot more test data into our table:
TRUNCATE TABLE ##TestTable
DECLARE @DateFrom DATE = '2012-01-01',
@DateTo DATE = '2015-08-14',
@UpdateDate1 DATE,
@UpdateDate2 DATE,
@UpdateDate3 DATE,
@i INT = 1
WHILE (@i < 1000000) --Value 1000000 is used for having enough data for testing. Please choose appropriate value for your server to avoid overloading it.
BEGIN
SET @UpdateDate1 = DATEADD(DAY, RAND(CHECKSUM(NEWID()))*(1+DATEDIFF(DAY, @DateFrom, @DateTo)), @DateFrom)
SET @UpdateDate2 = DATEADD(DAY, RAND(CHECKSUM(NEWID()))*(1+DATEDIFF(DAY, @DateFrom, @DateTo)), @DateFrom)
SET @UpdateDate3 = DATEADD(DAY, RAND(CHECKSUM(NEWID()))*(1+DATEDIFF(DAY, @DateFrom, @DateTo)), @DateFrom)
INSERT INTO ##TestTable(Name, UpdateByApp1Date, UpdateByApp2Date, UpdateByApp3Date )
VALUES(CAST(NEWID() AS NVARCHAR(36)), @UpdateDate1,@UpdateDate2,@UpdateDate3)
SET @i=@i+1
END
Now we run all four queries together in the same query window and include the "Actual Execution Plans".
--1
SELECT
ID,
(SELECT MAX(LastUpdateDate)
FROM (VALUES (UpdateByApp1Date),(UpdateByApp2Date),(UpdateByApp3Date)) AS UpdateDate(LastUpdateDate))
AS LastUpdateDate
FROM ##TestTable
--2
SELECT ID, MAX(UpdateDate) AS LastUpdateDate
FROM ##TestTable
UNPIVOT ( UpdateDate FOR DateVal IN ( UpdateByApp1Date, UpdateByApp2Date, UpdateByApp3Date ) ) AS u
GROUP BY ID, Name
--3
SELECT ID, MAX(UpdateDate) AS LastUpdateDate
FROM
(
SELECT ID, UpdateByApp1Date AS UpdateDate
FROM ##TestTable
UNION
SELECT ID, UpdateByApp2Date AS UpdateDate
FROM ##TestTable
UNION
SELECT ID, UpdateByApp3Date AS UpdateDate
FROM ##TestTable
) ud
GROUP BY ID
--4
SELECT ID,
( SELECT MAX(UpdateDate) AS LastUpdateDate
FROM
( SELECT tt.UpdateByApp1Date AS UpdateDate
UNION
SELECT tt.UpdateByApp2Date
UNION
SELECT tt.UpdateByApp3Date
) ud
) LastUpdateDate
FROM ##TestTable tt
In the actual execution plan we can see the Query Cost (relative to the batch) is 7% for the first query, 11% for the second, 16% for the third and 66% for the forth. We can also see that each query used a totally different execution plan.
We can run these queries separately and see the execution time for the first query is minimal (if the queries run very fast on your server, you can increase the rowcount in your testing table to have a more clear comparison of run times). So as we can see, the first query is the most optimal also it has compact code and is a good choice for calculating the maximum from the columns of the same data type. And not only the maximum or minimum: we can modify the code to find average, sum, etc. from a group of columns of the same data type.
We can also solve this task by creating a function, which finds the maximum or minimum from the given parameters - ufnGetMaximumDate(@Date1 datetime, @Date2 datetime, @Date3 datetime), but it's not a flexible solution, because it can be applied only to fixed number of columns with the same data type and we can only use this function for a specific task. When we need to compare values of more columns we would have to rewrite the function or create a new one, because in SQL Server we can't create a function with a dynamic number of parameters.
Conclusion
In SQL Server we can find the maximum or minimum value from different columns of the same data type using different methods. Performance and compact code are essential. As we can see the first solution in our article is the best in performance and it also has relatively compact code. Please consider these evaluations and comparisons are estimates, the performance you will see depends on table structure, indexes on columns, etc.
Next Steps
Review these related items:
About the author
Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2021-12-08
