Order by the highest of two values in MySQL?
Posted: Sun Apr 26, 2009 5:13 pm
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?
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?