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

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

Answer 2

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