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

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?

From stackoverflow pearcewg

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

  From Jason Punyon

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

  From Jobo

• 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
  

  From Charles Bretana

• 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
  

  From Russ Cam

• 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

  From Sam Saffron

• 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
  

  From Matt Hamilton