mysql order by 2 dates

Question

Hi i have a table with 2 dates the ADDDATE and UPDATEDATE

When add an item the ADDDATE is the current date and the UPDATEDATE is 0000-00-00

i need to ORDER BY the sql command and get the best date of those 2

For example if ADD=12/1/2010 and UPDATE=30/6/2010 the UPDATE DATE is best (newest)

Any help or reading appreciated

Answer 1

 ORDER BY IF(UPDATEDATE > ADDDATE, UPDATEDATE, ADDDATE)

Answer 2

Use GREATEST to find the newest date:

ORDER BY GREATEST(UPDATEDATE, ADDDATE)

Answer 3

If you want to make your database scalable, the best approach will be to add a new column LATESTDATE along with an insert/update trigger which sets that to the latest of the two candidate fields using, for example, greatest.

Running per-row functions on your selects slows down very fast as the table gets bigger. If you want performance to stay workable as the table grows, this sort of trick is a common one.

It technically violates 3NF since it duplicates one of the dates but violation is okay for performance reasons and you still maintain the ACID properties because the triggers dictate that the columns stay consistent.

I don't know the MySQL trigger syntax off the top of my head (I'm a DB2 man myself) but I would do the following:

• Create the new column, setting it to a suitable default value.
• Create the insert/update triggers to ensure changes will be reflected.
• Touch all the rows with a query like update TBL set ADDDATE = ADDDATE so that the trigger fires for all current rows.

The advantage this solution has is that it moves the cost of calculation to the time when data changes, not every single time you query the database. This amortises the cost across all reads, making it more efficient (unless you're one of those very rare beasts where the table is written more often than read).

Keep in mind this may not be necessary if the size of your table is relatively small - I work in environments where the tables are truly huge.