SQL - Getting Most Recent Date From Multiple Columns

Question

Assume a rowset containing the following

EntryID    Name      DateModified   DateDeleted
-----------------------------------------------  
1          Name1     1/2/2003       NULL
2          Name1     1/3/2005       1/5/2008
3          Name1     1/3/2006       NULL
4          Name1     NULL           NULL  
5          Name1     3/5/2008       NULL

Clarification:

I need a single value - the largest non-null date from BOTH columns. So the largest of all ten cells in this case.

Answer 1

The answer depends on what you really want. If you simply want the most recent of the two date values then you can do:

Select Max(DateModified), Max(DateDeleted)
From Table

If you are asking for the largest value from either column, then you can simply do:

Select Case 
        When Max(DateModified) > Max(DateDeleted) Then Max(DateModified)
        Else Max(DateDeleted)
        End As MaxOfEitherValue
From Table

Answer 2

SELECT MAX(CASE WHEN (DateDeleted IS NULL OR DateModified > DateDeleted)
                THEN DateModified ELSE DateDeleted END) AS MaxDate
FROM Table

Answer 3

As a general solution, you could try something like this:

select max(date_col)
from(
  select max(date_col1) AS date_col from some_table
  union
  select max(date_col2) AS date_col from some_table
  union
  select max(date_col3) AS date_col from some_table
  ...
)

There might be easier ways, depending on what database you're using.

Answer 4

How about;

SELECT MAX(MX) FROM (
    SELECT MAX(DateModified) AS MX FROM Tbl
    UNION
    SELECT MAX(DateDeleted) FROM Tbl
) T

Answer 5

For MySQL, Postgres or Oracle, use the GREATEST function:

SELECT GREATEST(ISNULL(t.datemodified, '1900-01-01 00:00:00'),  
                ISNULL(t.datedeleted, '1900-01-01 00:00:00'))
  FROM TABLE t

Both Oracle and MySQL will return NULL if a NULL is provided. The example uses MySQL null handling - update accordingly for the appropriate database.

A database agnostic alternative is:

SELECT z.entryid,
       MAX(z.dt)
  FROM (SELECT x.entryid,
               x.datemodified AS dt
          FROM TABLE x
        UNION ALL
        SELECT y.entryid
               y.datedeleted AS dt
          FROM TABLE y) z
GROUP BY z.entryid

Answer 6

The above are all valid answers;

But I'm Not sure if this would work?

select IsNull((
                select MAX(DateModified) 
                from table
              )
             ,
              (
                 select MAX(DateDeleted) 
                 from table
              )
             )     as MaxOfEitherValue
from    table 

Edit 1:

Whilst in the shower this morning, I had another solution:

Solution 2:

  select MAX(v) from (
                       select MAX(DateModified) as v from table
                       union all
                       select MAX(DateDeleted) as v from table
                     ) as SubTable

Edit 3:

Damn it, just spotted this is the same solution as Alex k. sigh...