SQL MAX of multiple columns?

Question

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?

Answer 1

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."

Answer 2

This is an old answer and broken in many way.

See https://stackoverflow.com/a/6871572/194653 which has way more upvotes and works with sql server 2008+ and handles nulls, etc.

Original but problematic answer:

Well, you can use the CASE statement:

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