Selecting most recent date between two columns

Question

If I have a table that (among other columns) has two DATETIME columns, how would I select the most recent date from those two columns.

Example:

ID     Date1     Date2

1      1/1/2008   2/1/2008

2      2/1/2008   1/1/2008

3      1/10/2008  1/10/2008

If I wanted my results to look like

ID     MostRecentDate

1      2/1/2008

2      2/1/2008

3      1/10/2008

Is there a simple way of doing this that I am obviously overlooking? I know I can do subqueries and case statements or even write a function in sql server to handle it, but I had it in my head that there was a max-compare type function already built in that I am just forgetting about.

Answer 1

Why couldn't you use the GREATEST function?

select id, date1, date2, GREATEST( nvl(date1,date2) , nvl(date2, date1) )
from table1;

I included a NVL to ensure that NULL was evaluated correctly, otherwise if either Date1 or Date2 is null, the Greatest returns NULL.

ID  Date1       Date2       MostRecentDate
1   1/1/2008    2/1/2008    2/1/2008
2   2/1/2008    1/1/2008    2/1/2008
3   1/10/2008   1/10/2008   1/10/2008
4   -null-      2/10/2008   2/10/2008
5   2/10/2008   -null-      2/10/2008

Answer 2

This thread has several solutions. If you had more than 2 dates to compare, "unpivot" might be preferable to writing a series of case statements. The following is blatantly stolen from Niikola:

select id, max(dDate) MostRecentDate
  from YourTable
    unpivot (dDate for nDate in (Date1, Date2, Date3)) as u
  group by id 

Then you can order by dDate, if that's helpful.

Answer 3

All other correct answers as already posted.

But if you are still really looking for MAX keyword then here is a way :

select ID , MAX(dt) from 
(  select Id , Date1 as dt from table1
   union  
   select ID , Date2 from table2
) d
group by d.Id

Answer 4

I think the accepted answer is the simplest. However, I would watch for null values in the dates...

SELECT ID,
       CASE WHEN ISNULL(Date1,'01-01-1753') > ISNULL(Date2,'01-01-1753') THEN Date1
            ELSE Date2
       END AS MostRecentDate
FROM Table

Answer 5

Other than case statement, I don't believe so...

  Select Case When DateColA > DateColB Then DateColA 
              Else DateColB End MostRecent
  From Table ... 

Answer 6

select ID,(select max(d) from (select Date1 d uninon select Date2 d) as t) as MaxDate
from MyTable