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.

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

Martin Smith · Accepted Answer · 2010-06-02T16:02:59.710

10

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

edited Jun 02 '10 at 16:02

answered Jun 02 '10 at 15:52

Martin Smith

419,657
83
708
800

2

That would need to be `CASE WHEN ISNULL(DateModified, 0) > ISNULL(DateDeleted, 0)` I think – Alex K. Jun 02 '10 at 15:58

OMG Ponies · Answer 2 · 2010-06-02T16:15:44.370

8

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

edited Jun 02 '10 at 16:15

answered Jun 02 '10 at 15:56

OMG Ponies

314,254
77
507
490

+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

score 4 · Answer 3 · answered Jun 02 '10 at 15:53

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.

Thomas · Answer 4 · 2010-06-02T15:57:03.593

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

score 2 · Answer 5 · answered Jun 02 '10 at 15:55

2

How about;

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

answered Jun 02 '10 at 15:55

Alex K.

165,803
30
257
277

Simple, works with most databases and multiple tables as well. – Gobe Jun 02 '15 at 15:41

Darknight · Answer 6 · 2010-06-03T07:38:39.433

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

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

SQL - Getting Most Recent Date From Multiple Columns

6 Answers6

Solution 2:

Linked