sort by most recent of 2 fields (both of which are dates)

[ghostsquad]

I'm creating a website and I want to display on my main page the most recent articles that have either been created or updated. Sorted by most recent of either.

Originally I came up with



SELECT
title,text,date_format(created,'%M %e, %Y') AS cf,date_format(updated,'%M %e, %Y') AS uf
FROM `journal_entries`
ORDER BY `updated` DESC,`created` DESC
LIMIT 0, 4


What would happen is, it would take all the "updated" entry, and display them first, regardless of if there was an entry created AFTER the most recently updated entry

for instance, the above query would give me something like this:



+-------------------+------------------+
| cf | uf |
+-------------------+------------------+
| January 1, 2008 | April 6, 2008 |
+-------------------+------------------+
| April 2, 2008 | April 5, 2008 |
+-------------------+------------------+
| April 7, 2008 | NULL |
+-------------------+------------------+
| April 2, 2008 | NULL |
+-------------------+------------------+
| March 18, 2008 | NULL |
+-------------------+------------------+

as you can see row 3 should be listed 1st, followed by 1, 2, 4, 5

I've looked into different things like GROUP BY, MAX(), UNION, nothing seems to be working.

Any help would be appreciated! Thanks!

[Nile]

Don't you need to put the * at the beginning, either before FROM or after SELECT?

[ghostsquad]

Don't you need to put the * at the beginning, either before FROM or after SELECT?

no. I'm not selecting every field, I'm selecting only a few. those fields are
'title'
'text'
'created' (which will be formatted and spit out as 'CF' (created formatted))
'updated' (which will be formatted and spit out as 'UF' (updated formatted))

oh, and the "table" I printed out does have the "title or text" like the query shows, because they aren't really necessary to explain what is going on.

[ghostsquad]

ok, I'm getting closer, but there is a problem with using "GREATEST", if the value is null, it returns null, not the OTHER value which would be greater.



SELECT title,text,date_format(created,'%M %e, %Y') AS cf,date_format(updated,'%M %e, %Y') AS uf, greatest(updated,created) AS sort_col FROM `journal_entries` ORDER BY sort_col DESC

[ghostsquad]

SOLVED:



SELECT
title,
text,
DATE_FORMAT(created,'%M %e, %Y') AS cf,
DATE_FORMAT(updated,'%M %e, %Y') AS uf,
IF(updated IS NULL,created,GREATEST(created,updated)) AS sort_col
FROM `journal_entries`
ORDER BY sort_col DESC


thanks to:
http://bugs.mysql.com/bug.php?id=15610