Find MAX value from multiple columns in a SQL Server table

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:

• Aggregate Functions
• Using PIVOT
• Using UNPIVOT
• SQL Server Execution Plan Overview

Sergey Gigoyan

Sergey Gigoyan (LinkedIn) is a Senior Technical Architect specializing in data and databases with more than 15 years of experience.  Sergey focuses on modern data architectures, database design and development, performance tuning and optimization, high availability solutions, BI development and DW design. He has worked with SQL Server, Oracle, and PostgreSQL databases, as well as cloud-based data solutions (AWS and Azure).  Sergey also has extensive experience with modern data stacks such as Snowflake and dbt.

Sergey’s experience spans various industries.  He had the privilege of working with IT giants such as Oracle as a Principal Data Engineer and BlackBerry as well as innovative startups.  He helped deliver complex database solutions and advanced data strategies.

Sergey is also the author of “Building a Successful Career in IT – How I Did It” where he provides actionable advice on thriving in the ever-evolving IT industry.

• MSSQLTips Awards: Champion (100+ tips) – 2024 | Author of the Year – 2020