Order rows by multiple cases on same column

Question

I have this structure

        |   start_at
----------------------
record1 |  2016-01-20 
record2 |  2016-01-15 
record3 |  2016-01-22 
record4 |  2016-01-10 

Lets say that the current date its 2016-01-19

I want to first get the records that have start_at greater than the current date and order them by start_at ASC.

Then I want to get the records that have start_at smaller than the current date and order them by start_at DESC.

So the results should be like this:

        |   start_at
----------------------
record1 |  2016-01-20
record3 |  2016-01-22
record2 |  2016-01-15
record4 |  2016-01-10

How can I do this?

I tried doing like this, but no success:

ORDER BY start_at >= NOW() ASC, start_at < NOW() DESC

Answer 1

You are close, with the multiple keys for the order by:

ORDER BY (start_at >= NOW()) desc,
         (case when start_at >= NOW() then start_at end) asc,
         (case when start_at < NOW() then start_at end) desc

The first condition puts the future dates first. How? The expression start_at >= NOW() returns a boolean. True values are "1" and false "0", so desc but the future values first. The other two keys handle the ordering within each group.