SQL/T-SQL - how to get the MAX value from 1 of N columns?

Question

In SQL/Transact SQL, I am trying to update a temp table to take the latest date from 3 different date columns (in that temp table), and put that MAX date into a "latest date" column.

What is the best way to do this, using an update statement?

Answer 1

I think you need to normalize your database from

RecordID, Column1, Column2, Column3

to

RecordID, ColumnID, Value

Then you'll be able to find the max value in the three columns easily...

Answer 2

You could use 3 'If' statements, starting with the first date. Then after the 3rd 'If' statement, you will know which is the highest (max) date...

Then store that in a variable and work from there.

Answer 3

With only three it's not so hard... This is definitely not extensible to an arbitrary number of datetime columns !

 Select Case When DT1 > DT2 And DT1 > DT3 Then DT1
             When DT2 > Dt3 Then Dt2 Else DT3 End
 From TableName

Answer 4

SELECT 
(CASE WHEN field_1 > field_2 THEN 
    CASE WHEN field_1 > field_3 THEN field_1 ELSE field_3 END
ELSE
    CASE WHEN field_2 > field_3 THEN field_2 ELSE field_3 END
END) AS maximum_date
FROM table

Answer 5

Oracle has the GREATEST statement, you can sort of simulate it with functions or case statements

See this question:

http://stackoverflow.com/questions/124417/is-there-a-max-function-in-sql-server-that-takes-two-values-like-mathmax-in-net

Answer 6

Perhaps a union?

select max(myDate)
from (
    select field_1 myDate from myTable where ...
    union all
    select field_2 myDate from myTable where ...
    union all
    select field_3 myDate from myTable where ...
) d

Of course, this hits the table three times for the same row. A CTE would probably solve that:

with myRow as (
    select field_1, field_2, field_3 from myTable where ...
)
select max(myDate)
from (
    select field_1 myDate from myRow
    union all
    select field_2 myDate from myRow
    union all
    select field_3 myDate from myRow
) d