I have a MySQL table that has two fields DateAdded and DateUpdated. When each row was added to the database DateAdded was set to NOW() and DateUpdated was set to 0000-00-00. Whenever a row was edited however, the DateUpdated field was set to NOW().
What I would like to do is to retrieve the table with the contents sorted by the highest date which for some fields will be DateAdded and others DateUpdated. So, my code would be something like this: SELECT * FROM table ORDER BY whatever_is_highest(DateAdded or DateUpdated) and the results should look something like this:
ID | DateAdded | DateUpdated |
1 | 2009-04-26 | 0000-00-00 |
2 | 2009-04-18 | 2009-04-25 |
3 | 2009-04-19 | 2009-04-24 |
4 | 2009-04-23 | 0000-00-00 |
5 | 2009-04-22 | 0000-00-00 |
This seems like it should be a fairly easy problem, but I haven't been able to find a solution yet. Can anyone point me in the right direction?
Order by the highest of two values in MySQL?
Moderator: General Moderators
Re: Order by the highest of two values in MySQL?
Ok, I figured it out:
SELECT * FROM table ORDER BY GREATEST(DateAdded, DateUpdated)
SELECT * FROM table ORDER BY GREATEST(DateAdded, DateUpdated)