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

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

[For Microsoft SQL Server 2008 and above, you may consider Sven's simpler answer below.]

Answer 2

I do not know if it is on SQL, etc... on M$ACCESS help there is a function called MAXA(Value1;Value2;...) that is supposed to do such.

Hope can help someone.

P.D.: Values can be columns or calculated ones, etc.