MySQL conditional ORDER BY ASC/DESC for date column

mysql, sql, sql-order-by

Solution

Try this:

ORDER BY post_status ASC,
CASE post_status WHEN 'future' THEN POST_DATE END ASC,
CASE WHEN post_status <> 'future' THEN post_date END DESC

Problem

I need a MySQL conditional ORDER BY statement for a datetime field. I have a table with posts which I would like to order in the following way: all future posts should be ordered ASC and all historical posts ordered DESC. Eg.: ``` post_status post_date post_title =========== ========= ========== future 2012-10-01 Title 1 future 2012-12-01 Title 2 publish 2012-05-01 Title 3 publish 2012-01-01 Title 4 ``` I need something similar to the following SQL... ``` SELECT post_status, post_date, post_title FROM wp_posts WHERE post_status IN ('future', 'publish') ORDER BY post_status ASC, CASE post_status WHEN 'future' THEN 'post_date ASC' ELSE 'post_date DESC' END; ``` Any hints on how to do this? Thanks!

Original source