SQL select most recent date multiple columns

Question

I have a table with an id and 2 datetime fields with over 2000 rows:

Example:

id created_at            updated_at
1  2015-07-07 13:39:38   NULL
2  2015-08-06 14:09:14   2015-11-18 10:20:55
3  2015-08-07 11:01:48   2015-11-12 16:43:11
...

How can i get a list sorted by the most recent datetime?

Order by updated_at DESC, created_at DESC gives me a wrong result. It orders by updated_at and then by created_at and doesn't give me the expected result.

I also tried several solutions with CASE WHEN, but it didn't work for me.

Correct sorted list for the example:

id created_at            updated_at
1  2015-08-06 14:09:14   2015-11-18 10:20:55
2  2015-08-07 11:01:48   2015-11-12 16:43:11
3  2015-07-07 13:39:38   NULL
...

Most recent of what? Of created_at, or of updated_at, or the most recent of these two values? — jarlh, Nov 18 '15 at 15:05

Juan Carlos Oropeza · Accepted Answer · 2015-11-18T15:16:32.533

3

Try GREATEST

http://www.w3resource.com/mysql/comparision-functions-and-operators/greatest-function.php

ORDER BY GREATEST(created_at, update_at) DESC

EDIT

You should check how handle null. I think using 0 or maybe use a default date '1900-1-1' please test it.

ORDER BY GREATEST(
                  COALESCE(created_at, 0),
                  COALESCE(update_at, 0)
                 ) DESC

edited Nov 18 '15 at 15:16

answered Nov 18 '15 at 15:04

Juan Carlos Oropeza

47,252
12
78
118

Of course, GREATEST is the way. – jarlh Nov 18 '15 at 15:09
@jarlh sorry bad spelling :$ – Juan Carlos Oropeza Nov 18 '15 at 15:10
Thanks this worked for me :) – tRx Nov 18 '15 at 15:12
@jarlh I include an update to handle `NULL` not 100% sure if that is correct, but something similar should work. – Juan Carlos Oropeza Nov 18 '15 at 15:18
I have no idea if that's needed or not. Doesn't GREATEST handle NULL as expected? – jarlh Nov 18 '15 at 15:20
@jarlh I saw this one. http://stackoverflow.com/questions/9831851/mysql-get-max-or-greatest-of-several-columns-but-with-null-fields Looks like doesnt. So just to be safe :) – Juan Carlos Oropeza Nov 18 '15 at 15:21

jarlh · Answer 2 · 2015-11-18T15:16:26.200

Something like this maybe?

select *
from tablename
order by case when created_at is null or created_at < update_at then update_at
              else created_at end desc

If created_at is NULL, or created_at is before update_at, then update_at is most recent. Otherwise created_at is most recent. Order by that value desc.

This also works for dbms products without MySQL's GREATEST function. If running MySQL, check out Juan Carlos Oropeza's answer instead of this.

SQL select most recent date multiple columns

2 Answers2