SQL MAX of multiple columns?

Here is another nice solution for the Max functionality using T-SQL and SQL Server

SELECT [Other Fields], (SELECT Max(v) FROM (VALUES (date1), (date2), (date3),...) AS value(v)) as [MaxDate] FROM [YourTableName] 

Values is the Table Value Constructor.

"Specifies a set of row value expressions to be constructed into a table. The Transact-SQL table value constructor allows multiple rows of data to be specified in a single DML statement. The table value constructor can be specified either as the VALUES clause of an INSERT ... VALUES statement, or as a derived table in either the USING clause of the MERGE statement or the FROM clause."

If you're using SQL Server 2022+ (or MySQL or PostgreSQL or Oracle or BigQuery), you can use

SELECT GREATEST(col1, col2 ...) FROM table 

There are 3 more methods where UNPIVOT (1) is the fastest by far, followed by Simulated Unpivot (3) which is much slower than (1) but still faster than (2)

CREATE TABLE dates ( number INT PRIMARY KEY , date1 DATETIME , date2 DATETIME , date3 DATETIME , cost INT ) INSERT INTO dates VALUES ( 1, '1/1/2008', '2/4/2008', '3/1/2008', 10 ) INSERT INTO dates VALUES ( 2, '1/2/2008', '2/3/2008', '3/3/2008', 20 ) INSERT INTO dates VALUES ( 3, '1/3/2008', '2/2/2008', '3/2/2008', 30 ) INSERT INTO dates VALUES ( 4, '1/4/2008', '2/1/2008', '3/4/2008', 40 ) GO 

Solution 1 (UNPIVOT)

SELECT number , MAX(dDate) maxDate , cost FROM dates UNPIVOT ( dDate FOR nDate IN ( Date1, Date2, Date3 ) ) as u GROUP BY number , cost GO 

Solution 2 (Sub query per row)

SELECT number , ( SELECT MAX(dDate) maxDate FROM ( SELECT d.date1 AS dDate UNION SELECT d.date2 UNION SELECT d.date3 ) a ) MaxDate , Cost FROM dates d GO 

Solution 3 (Simulated UNPIVOT)

;WITH maxD AS ( SELECT number , MAX(CASE rn WHEN 1 THEN Date1 WHEN 2 THEN date2 ELSE date3 END) AS maxDate FROM dates a CROSS JOIN ( SELECT 1 AS rn UNION SELECT 2 UNION SELECT 3 ) b GROUP BY Number ) SELECT dates.number , maxD.maxDate , dates.cost FROM dates INNER JOIN MaxD ON dates.number = maxD.number GO DROP TABLE dates GO 

Scalar Function cause all sorts of performance issues, so its better to wrap the logic into an Inline Table Valued Function if possible. This is the function I used to replace some User Defined Functions which selected the Min/Max dates from a list of upto ten dates. When tested on my dataset of 1 Million rows the Scalar Function took over 15 minutes before I killed the query the Inline TVF took 1 minute which is the same amount of time as selecting the resultset into a temporary table. To use this call the function from either a subquery in the the SELECT or a CROSS APPLY.

CREATE FUNCTION dbo.Get_Min_Max_Date ( @Date1 datetime, @Date2 datetime, @Date3 datetime, @Date4 datetime, @Date5 datetime, @Date6 datetime, @Date7 datetime, @Date8 datetime, @Date9 datetime, @Date10 datetime ) RETURNS TABLE AS RETURN ( SELECT Max(DateValue) Max_Date, Min(DateValue) Min_Date FROM ( VALUES (@Date1), (@Date2), (@Date3), (@Date4), (@Date5), (@Date6), (@Date7), (@Date8), (@Date9), (@Date10) ) AS Dates(DateValue) ) 

Either of the two samples below will work:

SELECT MAX(date_columns) AS max_date FROM ( (SELECT date1 AS date_columns FROM data_table ) UNION ( SELECT date2 AS date_columns FROM data_table ) UNION ( SELECT date3 AS date_columns FROM data_table ) ) AS date_query 

The second is an add-on to lassevk's answer.

SELECT MAX(MostRecentDate) FROM ( SELECT CASE WHEN date1 >= date2 AND date1 >= date3 THEN date1 WHEN date2 >= date1 AND date2 >= date3 THEN date2 WHEN date3 >= date1 AND date3 >= date2 THEN date3 ELSE date1 END AS MostRecentDate FROM data_table ) AS date_query 

For T-SQL (MSSQL 2008+)

SELECT (SELECT MAX(MyMaxName) FROM ( VALUES (MAX(Field1)), (MAX(Field2)) ) MyAlias(MyMaxName) ) FROM MyTable1 

DECLARE @TableName TABLE (Number INT, Date1 DATETIME, Date2 DATETIME, Date3 DATETIME, Cost MONEY) INSERT INTO @TableName SELECT 1, '20000101', '20010101','20020101',100 UNION ALL SELECT 2, '20000101', '19900101','19980101',99 SELECT Number, Cost , (SELECT MAX([Date]) FROM (SELECT Date1 AS [Date] UNION ALL SELECT Date2 UNION ALL SELECT Date3 ) D ) [Most Recent Date] FROM @TableName 

SELECT CASE WHEN Date1 >= Date2 AND Date1 >= Date3 THEN Date1 WHEN Date2 >= Date3 THEN Date2 ELSE Date3 END AS MostRecentDate 

This is slightly easier to write out and skips evaluation steps as the case statement is evaluated in order.

Unfortunately Lasse's answer, though seemingly obvious, has a crucial flaw. It cannot handle NULL values. Any single NULL value results in Date1 being returned. Unfortunately any attempt to fix that problem tends to get extremely messy and doesn't scale to 4 or more values very nicely.

databyss's first answer looked (and is) good. However, it wasn't clear whether the answer would easily extrapolate to 3 values from a multi-table join instead of the simpler 3 values from a single table. I wanted to avoid turning such a query into a sub-query just to get the max of 3 columns, also I was pretty sure databyss's excellent idea could be cleaned up a bit.

So without further ado, here's my solution (derived from databyss's idea).
It uses cross-joins selecting constants to simulate the effect of a multi-table join. The important thing to note is that all the necessary aliases carry through correctly (which is not always the case) and this keeps the pattern quite simple and fairly scalable through additional columns.

DECLARE @v1 INT , @v2 INT , @v3 INT --SET @v1 = 1 --Comment out SET statements to experiment with --various combinations of NULL values SET @v2 = 2 SET @v3 = 3 SELECT ( SELECT MAX(Vals) FROM ( SELECT v1 AS Vals UNION SELECT v2 UNION SELECT v3 ) tmp WHERE Vals IS NOT NULL -- This eliminates NULL warning ) AS MaxVal FROM ( SELECT @v1 AS v1 ) t1 CROSS JOIN ( SELECT @v2 AS v2 ) t2 CROSS JOIN ( SELECT @v3 AS v3 ) t3 

Finally, for the following:

• SQL Server 2022 (16.x) Preview
• Azure SQL Database
• Azure SQL Managed Instance

we can use GREATEST, too. Similar to other T-SQL functions, here are few important notes:

• if all arguments have the same data type and the type is supported for comparison, GREATEST will return that type;
• otherwise, the function will implicitly convert all arguments to the data type of the highest precedence before comparison and use this type as the return type;
• if one or more arguments are not NULL, then NULL arguments will be ignored during comparison; if all arguments are NULL, then GREATEST will return NULL;

The following types are not supported for comparison in GREATEST: varchar(max), varbinary(max) or nvarchar(max) exceeding 8,000 bytes, cursor, geometry, geography, image, non-byte-ordered user-defined types, ntext, table, text, and xml.

Problem: choose the minimum rate value given to an entity Requirements: Agency rates can be null

[MinRateValue] = CASE WHEN ISNULL(FitchRating.RatingValue, 100) < = ISNULL(MoodyRating.RatingValue, 99) AND ISNULL(FitchRating.RatingValue, 100) < = ISNULL(StandardPoorsRating.RatingValue, 99) THEN FitchgAgency.RatingAgencyName WHEN ISNULL(MoodyRating.RatingValue, 100) < = ISNULL(StandardPoorsRating.RatingValue , 99) THEN MoodyAgency.RatingAgencyName ELSE ISNULL(StandardPoorsRating.RatingValue, 'N/A') END 

Inspired by this answer from Nat

If you are using SQL Server 2005, you can use the UNPIVOT feature. Here is a complete example:

create table dates ( number int, date1 datetime, date2 datetime, date3 datetime ) insert into dates values (1, '1/1/2008', '2/4/2008', '3/1/2008') insert into dates values (1, '1/2/2008', '2/3/2008', '3/3/2008') insert into dates values (1, '1/3/2008', '2/2/2008', '3/2/2008') insert into dates values (1, '1/4/2008', '2/1/2008', '3/4/2008') select max(dateMaxes) from ( select (select max(date1) from dates) date1max, (select max(date2) from dates) date2max, (select max(date3) from dates) date3max ) myTable unpivot (dateMaxes For fieldName In (date1max, date2max, date3max)) as tblPivot drop table dates 

Using CROSS APPLY (for 2005+) ....

SELECT MostRecentDate FROM SourceTable CROSS APPLY (SELECT MAX(d) MostRecentDate FROM (VALUES (Date1), (Date2), (Date3)) AS a(d)) md 

From SQL Server 2012 we can use IIF.

 DECLARE @Date1 DATE='2014-07-03'; DECLARE @Date2 DATE='2014-07-04'; DECLARE @Date3 DATE='2014-07-05'; SELECT IIF(@Date1>@Date2, IIF(@Date1>@Date3,@Date1,@Date3), IIF(@Date2>@Date3,@Date2,@Date3)) AS MostRecentDate 

Please try using UNPIVOT:

SELECT MAX(MaxDt) MaxDt FROM tbl UNPIVOT (MaxDt FOR E IN (Date1, Date2, Date3) )AS unpvt; 

I prefer solutions based on case-when, my assumption is that it should have the least impact on possible performance drop compared to other possible solutions like those with cross-apply, values(), custom functions etc.

Here is the case-when version that handles null values with most of possible test cases:

SELECT CASE WHEN Date1 > coalesce(Date2,'0001-01-01') AND Date1 > coalesce(Date3,'0001-01-01') THEN Date1 WHEN Date2 > coalesce(Date3,'0001-01-01') THEN Date2 ELSE Date3 END AS MostRecentDate , * from (values ( 1, cast('2001-01-01' as Date), cast('2002-01-01' as Date), cast('2003-01-01' as Date)) ,( 2, cast('2001-01-01' as Date), cast('2003-01-01' as Date), cast('2002-01-01' as Date)) ,( 3, cast('2002-01-01' as Date), cast('2001-01-01' as Date), cast('2003-01-01' as Date)) ,( 4, cast('2002-01-01' as Date), cast('2003-01-01' as Date), cast('2001-01-01' as Date)) ,( 5, cast('2003-01-01' as Date), cast('2001-01-01' as Date), cast('2002-01-01' as Date)) ,( 6, cast('2003-01-01' as Date), cast('2002-01-01' as Date), cast('2001-01-01' as Date)) ,( 11, cast(NULL as Date), cast('2002-01-01' as Date), cast('2003-01-01' as Date)) ,( 12, cast(NULL as Date), cast('2003-01-01' as Date), cast('2002-01-01' as Date)) ,( 13, cast('2003-01-01' as Date), cast(NULL as Date), cast('2002-01-01' as Date)) ,( 14, cast('2002-01-01' as Date), cast(NULL as Date), cast('2003-01-01' as Date)) ,( 15, cast('2003-01-01' as Date), cast('2002-01-01' as Date), cast(NULL as Date)) ,( 16, cast('2002-01-01' as Date), cast('2003-01-01' as Date), cast(NULL as Date)) ,( 21, cast('2003-01-01' as Date), cast(NULL as Date), cast(NULL as Date)) ,( 22, cast(NULL as Date), cast('2003-01-01' as Date), cast(NULL as Date)) ,( 23, cast(NULL as Date), cast(NULL as Date), cast('2003-01-01' as Date)) ,( 31, cast(NULL as Date), cast(NULL as Date), cast(NULL as Date)) ) as demoValues(id, Date1,Date2,Date3) order by id ; 

and the result is:

MostRecent id Date1 Date2 Date3 2003-01-01 1 2001-01-01 2002-01-01 2003-01-01 2003-01-01 2 2001-01-01 2003-01-01 2002-01-01 2003-01-01 3 2002-01-01 2001-01-01 2002-01-01 2003-01-01 4 2002-01-01 2003-01-01 2001-01-01 2003-01-01 5 2003-01-01 2001-01-01 2002-01-01 2003-01-01 6 2003-01-01 2002-01-01 2001-01-01 2003-01-01 11 NULL 2002-01-01 2003-01-01 2003-01-01 12 NULL 2003-01-01 2002-01-01 2003-01-01 13 2003-01-01 NULL 2002-01-01 2003-01-01 14 2002-01-01 NULL 2003-01-01 2003-01-01 15 2003-01-01 2002-01-01 NULL 2003-01-01 16 2002-01-01 2003-01-01 NULL 2003-01-01 21 2003-01-01 NULL NULL 2003-01-01 22 NULL 2003-01-01 NULL 2003-01-01 23 NULL NULL 2003-01-01 NULL 31 NULL NULL NULL 

Based on the ScottPletcher's solution from http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_24204894.html I’ve created a set of functions (e.g. GetMaxOfDates3 , GetMaxOfDates13 )to find max of up to 13 Date values using UNION ALL. See T-SQL function to Get Maximum of values from the same row However I haven't considered UNPIVOT solution at the time of writing these functions

CREATE FUNCTION GetMaxOfDates13 ( @value01 DateTime = NULL, @value02 DateTime = NULL, @value03 DateTime = NULL, @value04 DateTime = NULL, @value05 DateTime = NULL, @value06 DateTime = NULL, @value07 DateTime = NULL, @value08 DateTime = NULL, @value09 DateTime = NULL, @value10 DateTime = NULL, @value11 DateTime = NULL, @value12 DateTime = NULL, @value13 DateTime = NULL ) RETURNS DateTime AS BEGIN RETURN ( SELECT TOP 1 value FROM ( SELECT @value01 AS value UNION ALL SELECT @value02 UNION ALL SELECT @value03 UNION ALL SELECT @value04 UNION ALL SELECT @value05 UNION ALL SELECT @value06 UNION ALL SELECT @value07 UNION ALL SELECT @value08 UNION ALL SELECT @value09 UNION ALL SELECT @value10 UNION ALL SELECT @value11 UNION ALL SELECT @value12 UNION ALL SELECT @value13 ) AS [values] ORDER BY value DESC ) END –FUNCTION GO CREATE FUNCTION GetMaxOfDates3 ( @value01 DateTime = NULL, @value02 DateTime = NULL, @value03 DateTime = NULL ) RETURNS DateTime AS BEGIN RETURN dbo.GetMaxOfDates13(@value01,@value02,@value03,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL,NULL) END –FUNCTION 

You could create a function where you pass the dates and then add the function to the select statement like below. select Number, dbo.fxMost_Recent_Date(Date1,Date2,Date3), Cost

create FUNCTION fxMost_Recent_Date 

( @Date1 smalldatetime, @Date2 smalldatetime, @Date3 smalldatetime ) RETURNS smalldatetime AS BEGIN DECLARE @Result smalldatetime

declare @MostRecent smalldatetime set @MostRecent='1/1/1900' if @Date1>@MostRecent begin set @MostRecent=@Date1 end if @Date2>@MostRecent begin set @MostRecent=@Date2 end if @Date3>@MostRecent begin set @MostRecent=@Date3 end RETURN @MostRecent 

END

Another way to use CASE WHEN

SELECT CASE true WHEN max(row1) >= max(row2) THEN CASE true WHEN max(row1) >= max(row3) THEN max(row1) ELSE max(row3) end ELSE CASE true WHEN max(row2) >= max(row3) THEN max(row2) ELSE max(row3) END END FROM yourTable 

My solution can handle null value comparison as well. It can be simplified by writing as one single query but for an explanation, I am using CTE. The idea is to reduce the comparison from 3 number to 2 number in step 1 and then from 2 number to 1 number in step 2.

with x1 as ( select 1 as N1, null as N2, 3 as N3 union select 1 as N1, null as N2, null as N3 union select null as N1, null as N2, null as N3 ) ,x2 as ( select N1,N2,N3, IIF(Isnull(N1,0)>=Isnull(N2,0),N1,N2) as max1, IIF(Isnull(N2,0)>=Isnull(N3,0),N2,N3) as max2 from x1 ) ,x3 as ( select N1,N2,N3,max1,max2, IIF(IsNull(max1,0)>=IsNull(max2,0),max1,max2) as MaxNo from x2 ) select * from x3 

Output: