mssqltips logo

Find MAX value from multiple columns in a SQL Server table

By:   |   Updated: 2015-10-20   |   Comments (10)   |   Related: More > T-SQL

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
	

sample data

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:

maximum value from multiple columns

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 @[email protected]+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.

sql server execution plans

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:



Last Updated: 2015-10-20


get scripts

next tip button



About the author
MSSQLTips author Sergey Gigoyan 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.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name
*Email
Email me updates

Signup for our newsletter

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.





Thursday, October 10, 2019 - 3:49:41 AM - Sergey Gigoyan Back To Top

Tom,

Thank you for your question. Please try this:

SELECT ID, LastUpdateDate, CASE WHEN LastUpdateDate = UpdateByApp1Date THEN 'UpdatedByApp1'
                                WHEN LastUpdateDate = UpdateByApp2Date THEN 'UpdatedByApp2'
WHEN LastUpdateDate = UpdateByApp3Date THEN 'UpdatedByApp3'
   END
   AS LastUpdatedByApp
FROM 
(     
SELECT 
   ID, UpdateByApp1Date,UpdateByApp2Date,UpdateByApp3Date,
   (SELECT MAX(LastUpdateDate)
      FROM (VALUES (UpdateByApp1Date),(UpdateByApp2Date),(UpdateByApp3Date)) AS UpdateDate(LastUpdateDate)) 
   AS LastUpdateDate
FROM ##TestTable 

) t

To practise, you can use the following link and try the exercises 4.1, 4.5, 5.7: https://dbprofi.com/exercises/

Thanks,
Sergey


Wednesday, October 09, 2019 - 8:48:36 AM - Tom Back To Top

Sergey,

This is a great solution to a problem I am facing, but how do you get the associated column name of the max column?  In other words, in a series of dates as above, producing; Company, [ColumnNameofMaxDateColumn], [MaxDateForThisRow] ?

Company         Column                            Date(max)
===================================
ABC                 UpdateByApp3                 2015-08-06


Friday, February 01, 2019 - 4:33:06 AM - Roberto Back To Top

Hi Sergey,

many thanks by your contribution, so useful.


Saturday, February 03, 2018 - 6:07:34 AM - Izhar Azati Back To Top

 We can use IIF (or CASE) (up to 10 level!):

SELECT  ID

, iif(tt.UpdateByApp1Date > tt.UpdateByApp2Date AND tt.UpdateByApp1Date > tt.UpdateByApp3Date

, tt.UpdateByApp1Date

, iif(tt.UpdateByApp2Date > tt.UpdateByApp1Date AND tt.UpdateByApp2Date > tt.UpdateByApp3Date

, tt.UpdateByApp2Date

, tt.UpdateByApp3Date)

)  AS LastUpdateDate

FROM    ##TestTable tt;


Friday, February 02, 2018 - 1:34:02 PM - Joe Celko Back To Top

 The first solution is also ANSI/ISO Standard SQL code. 


Thursday, October 12, 2017 - 11:35:12 AM - Roopesh das Back To Top

 I have a table(temp table) created with cols location and and vehicle type , insterted some rows .

Location VehicleTyp

WAS       BUS

WAS       TRN

NYP        BUS

LAX        TRN

Trying to filter and get rows for which locaiton has one type not more than one like here NYP has one type row 'BUS' no recond type for NYP. But WAS has 'BUS' and 'TRN' so I would like to filter only NYP get one row from query as WAS has 2 types but NYP has only one type 'BUS" no second row with another type. LAX is automatically filtered as we are looking for locaitons that has only olne row of 'BUS' type. Can we frame a query instead of loop.

 

 


Thursday, March 31, 2016 - 5:35:29 PM - Patrick Back To Top

How would you return a *different* column based on the max value? For example (please ignore the un-normalized data, I don't have a choice in the structure) if you had 12 columns, ReportName1, ReportDate2, ReportName2, ReportDate2, ..., ReportName6, ReportDate6 and wanted to return the NAME of the report that was run most recently.

TIA


Saturday, February 20, 2016 - 12:54:35 PM - Sergey Gigoyan Back To Top

Hi Elena,

You you can do this easily by choosing the first solution:

DECLARE @ID INT =789

 

SELECT 

   (SELECT MAX(highestdata)

      FROM (VALUES (Payroll),(ERS_Data),(SUPP_Data)) AS maxnumber(highestdata)) 

   AS highestdata

FROM market_data 

 

WHERE [email protected]

 

However, if you need to use UNPIVOT, the following two solutions will solve your problem:

--1

DECLARE @ID INT=789 

 

SELECT  MAX(highestdata) AS maxnumber

FROM 

(SELECT Payroll, ERS_Data, SUPP_Data FROM market_data WHERE [email protected]) AS t

UNPIVOT (highestdata FOR Val IN (Payroll, ERS_Data, SUPP_Data)) AS Number

 

--2

DECLARE @ID INT=789 

 

;WITH market_data_CTE (Payroll, ERS_Data, SUPP_Data)

AS

 

(

    SELECT Payroll, ERS_Data, SUPP_Data 

FROM market_data WHERE [email protected]

)

SELECT  MAX(highestdata) AS maxnumber

FROM 

market_data_CTE

 

UNPIVOT (highestdata FOR Val IN (Payroll, ERS_Data, SUPP_Data)) AS Number

 

Thank you for reading


Friday, February 19, 2016 - 6:21:16 PM - Elena Back To Top

Hi Mr. Gigoyan,

I find your article very helpful. I tried the UNPIVOT function to find the MAX value of multiple columns in my tables.

However, I am having a problem filtering the results. It is getting the maximum value, the problem is, that value shows in every row (every record, regardless of record ID).

I am using CASPIO (a cloud database builder), and it appears "SELECT ID", and "GROUP BY" are not supported for some reason.

Can you please recommend, other ways to filter the results so that it is appropriate for each record?

Below is my sample database and my code:

ID      Payroll     ERS_Data      SUPP_Data
123    $89,000    $97,900.0    $91,670.00
456    $76,200    $83,820.0    $78,486.00
789    $54,300    $55,321       $59,730.0

The query that I adopted from your article (I customized for my purpose):

SELECT  MAX(highestdata) AS maxnumber
FROM market_data
UNPIVOT (highestdata FOR Val IN (Payroll, ERS_Data, SUPP_Data)) AS Number
WHERE ID = [@field:ID_Lookup]

 

As you can see, I tried to filter by using "WHERE", but it's not working.

Thank you for your help. Thank you for posting the article.

Elena

 

 

 

 

 

 

 

 

 


Tuesday, October 20, 2015 - 8:52:20 AM - Thomas Franz Back To Top

Good article.

Regarding the not recommended ufnGetMaximumDate: It has also the drawback that scalar functions perform very bad AND the execution plan is lying about its costs (always zero) so that you have to run a performance test with several thousand rows and compare the CPU time (I/O is irrelevant in this case; you would need extended events or the Profiler to find the real reads when using a function).



download

























get free sql tips

I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



Learn more about SQL Server tools