MySQL conditional ORDER BY ASC/DESC for date column

Question

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!

score 25 · Accepted Answer · answered May 16 '12 at 20:47

25

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

answered May 16 '12 at 20:47

rs.

26,707
12
68
90

1

This is a better solution because it only requires a single scan of the table. – Jeff Wu May 16 '12 at 20:59

score 2 · Answer 2 · answered May 17 '12 at 06:51

2

Try this one -

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);

answered May 17 '12 at 06:51

Devart

119,203
23
166
186

earth_tom · Answer 3 · 2012-05-16T21:05:26.220

1

How about something like this? Select twice and union 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

edited May 16 '12 at 21:05

answered May 16 '12 at 20:46

earth_tom

831
1
5
13

J-16 SDiZ · Answer 4 · 2012-05-18T16:02:17.107

i would use an union all, tricks in order by can't use index and is 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;

MySQL conditional ORDER BY ASC/DESC for date column

4 Answers4

Linked