MySQL conditional ORDER BY ASC / DESC for date column

Question

I need a conditional ORDER BY MySQL statement for a datetime field. I have a table with messages that I would like to order as follows: all future posts must be ordered by ASC and all historical records are ordered by DESC . For instance:.

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 clues on how to do this? Thanks!

Answer 1

Try the following:

 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 

Answer 2

How about something like that? Select twice and combine the results.

 Select * from (SELECT post_status, post_date, post_title FROM wp_posts WHERE post_status IN ('future') ORDER BY post_status ASC ) alias1 UNION Select * from (SELECT post_status, post_date, post_title FROM wp_posts WHERE post_status IN ('publish') ORDER BY post_status DESC ) alias2 

Answer 3

Try this option -

 SELECT post_status, post_date, post_title FROM wp_posts WHERE post_status IN ('future', 'publish') ORDER BY IF(post_status = 'future', 0, 1), IF(post_status = 'future', TO_DAYS(post_date), TO_DAYS(post_date) * -1); 

Answer 4

I would use union all , tricks in order by cannot use index and slower.

 SELECT * FROM ((SELECT 1 AS a, @rownum: =@rownum +1 B, post_status, post_date, post_title FROM wp_posts, (SELECT @rownum:=0) r WHERE post_status='publish' ORDER BY post_date DESC) UNION ALL (SELECT 2 AS a, @rownum: =@rownum +1 B, post_status, post_date, post_title FROM wp_posts, (SELECT @rownum:=0) r2 WHERE post_status='future' ORDER BY post_date)) ORDER BY A,B;