SQL - Getting Most Recent Date From Multiple Columns

Assume a rowset containing the following

EntryID    Name      DateModified   D开发者_如何转开发ateDeleted
-----------------------------------------------  
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.

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

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

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.

How about;

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

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

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

How to find the Latest Date from the columns from Multiple tables e.g. if the Firstname is in Table1, Address is in Table2, Phone is in Table3:

When you are using with main SELECT statement while selecting other columns it is best written as :

SELECT Firstname
,Lastname
,Address
,PhoneNumber
,

,(SELECT max(T.date_col) from(select max(date_col1) AS date_col from Table1 Where ..
                               union
                               select max(date_col2) AS date_col from Table2 Where..
                               union
                               select max(date_col3) AS date_col from Table3 Where..
                               ) AS T
   ) AS Last_Updated_Date

FROM Table T1
LEFT JOIN Table T2 ON T1.Common_Column=T2.Common_Column
LEFTJOIN Table T3 ON T1.Common_Column=T3.Common_Column