CODEWITHSUNDEEP
mysqlsql
Richard
Richard

Reputation: 4415

Sort records by; future ASC, past DESC

I want to sort records as follows:

  1. Future/present events ASC
  2. Past events DESC

So first today, then tomorrow, until there are no more future records. Then I want to show the past events, but the latest first.

So far I've found a solution for the first point:

ORDER BY (
    CASE WHEN ev.StartDate < CURDATE()
          THEN 1
          ELSE 0
     END) ASC, ev.StartDate ASC

But the issue with this query is that all posts are ordered ASC, including the past posts (which need to be DESC).

How do I combine this in the CASE?

Upvotes: 5

Views: 1881

Answers (4)

Stalker
Stalker

Reputation: 168

I had the same requirement and found another way

ORDER BY (CURDATE()>ev.StartDate) ASC, ABS(DATEDIFF(CURDATE(),ev.StartDate))

Upvotes: 1

Devon
Devon

Reputation: 43

I find this most straight forward, without needing complex conditional syntax:

first one ranks future before past, second one orders the future ASC, third one orders the past DESC

(second and third ones are interchangeable)

ORDER BY
  (date < CURDATE()) ASC,
  (greatest(date, CURDATE()) ASC,
  (least(date, CURDATE()) DESC

Upvotes: 2

Jaugar Chang
Jaugar Chang

Reputation: 3196

ORDER BY 
CASE WHEN (CURDATE() > ev.StartDate)
THEN datediff(CURDATE(),ev.StartDate )  --Past, older date bigger differ
ELSE datediff(ev.StartDate , CURDATE()+100) END  --Future, differ from a more futrue date

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269973

You need a slightly more complex order by:

ORDER BY (ev.StartDate < CURDATE()),
         (case when ev.StartDate > CURDATE() then ev.StartDate end) ASC,
         (case when ev.StartDate < CURDATE() then ev.StartDate end) DESC

You could actually do this with two clauses:

ORDER BY greatest(ev.StartDate, CURDATE()) DESC,
         least(ev.StartDate, CURDATE()) ASC

But I think the first version is clearer in its intention.

Upvotes: 16

Related Questions