SQL MAX of multiple columns?

How do you return 1 value per row of the max of several columns:

**TableName**

[Number, Date1, Date2, Date3, Cost]

I need to return something like this:

[Number, Most_Recent_Date, Cost]

Query?

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

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.

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

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

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)
)

Unfortunately [Lasse's answer](

[To see links please register here]

), 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](

[To see links please register here]

) 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

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][1] 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

[1]:

[To see links please register here]

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](

[To see links please register here]

) from [Nat](

[To see links please register here]

)

Please try using `UNPIVOT`:

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