Sort records by; future ASC, past DESC

Question

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?

Answer 1

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.