10

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.

Scott Baker
  • 10,013
  • 17
  • 56
  • 102
  • Are you asking for the largest value from *either* DateModified *or* DateDeleted or do you want to largest value *for each* column? – Thomas Jun 02 '10 at 15:55
  • @OMG - does that make a difference? @Thomas - I have edited my question; just one value from both columns – Scott Baker Jun 02 '10 at 23:04
  • 2
    I think the reason for @OMG's question was to know whether the `GREATEST` function would be available. – Martin Smith Jun 02 '10 at 23:07

7 Answers7

10
SELECT MAX(CASE WHEN (DateDeleted IS NULL OR DateModified > DateDeleted)
                THEN DateModified ELSE DateDeleted END) AS MaxDate
FROM Table
Martin Smith
  • 438,706
  • 87
  • 741
  • 845
9

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
OMG Ponies
  • 325,700
  • 82
  • 523
  • 502
  • +1 I didn't know about the `GREATEST` function, plus, I like your database agnostic solution. =) – Will Marcouiller Jun 02 '10 at 16:00
  • +1 for the `GREATEST` function. That makes things a lot easier (assuming his database supports it)! – FrustratedWithFormsDesigner Jun 02 '10 at 16:02
  • 2
    Your first solution does not work. 1) Greatest is not an aggregate function, it returns a result per row, so need `MAX(GREATEST(...)` 2) Except that (at least on Oracle) `GREATEST(some_value, NULL)` returns `NULL`, so would not meet the specific needs of OP. – Shannon Severance Jun 02 '10 at 16:06
  • FYI: SQLite GREATEST equivalent (which isn't supported by SQL Server or MySQL btw): http://stackoverflow.com/questions/2166690/sqlite-equivilant-of-postgresql-greatest-function – OMG Ponies Jun 02 '10 at 16:11
  • @Shannon Severance: The OP isn't asking for an aggregate. MySQL also returns NULL if comparing dates; I provided MySQL syntax because it's the most likely vendor. – OMG Ponies Jun 02 '10 at 16:14
4

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.

FrustratedWithFormsDesigner
  • 26,726
  • 31
  • 139
  • 202
2

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
Thomas
  • 63,911
  • 12
  • 95
  • 141
2

How about;

SELECT MAX(MX) FROM (
    SELECT MAX(DateModified) AS MX FROM Tbl
    UNION
    SELECT MAX(DateDeleted) FROM Tbl
) T
Alex K.
  • 171,639
  • 30
  • 264
  • 288
1

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

Darknight
  • 2,460
  • 2
  • 22
  • 26
  • No this wouldn't work in the case that both were not null. Also the `from table` means it will return multiple rows. – Martin Smith Jun 02 '10 at 16:28
-1

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
Ivan Starostin
  • 8,798
  • 5
  • 21
  • 39
user19456276
  • 1