Order by the highest of two values in MySQL?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
joshmaker
Forum Commoner
Posts: 25
Joined: Mon May 15, 2006 2:53 pm
Location: Arlington VA

Order by the highest of two values in MySQL?

Post by joshmaker »

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?
joshmaker
Forum Commoner
Posts: 25
Joined: Mon May 15, 2006 2:53 pm
Location: Arlington VA

Re: Order by the highest of two values in MySQL?

Post by joshmaker »

Ok, I figured it out:
SELECT * FROM table ORDER BY GREATEST(DateAdded, DateUpdated)
Post Reply