CODEWITHSUNDEEP
mysql
Mike F
Mike F

Reputation: 37

MySQL ORDER BY conditional second

Selecting from a table events including columns start_date and ongoing (boolean), I would like to:

  1. Sort results first by ongoing (all 0s before 1s).
  2. Then I would like to sub-sort all non-ongoing events (ongoing = 0) by start_date ASC and all ongoing events (ongoing = 1) by start_date DESC. I'm probably missing something stupid simple (maybe using CASE?), but I'm stuck!

TIA

Upvotes: 0

Views: 29

Answers (1)

Peter Bowers
Peter Bowers

Reputation: 3093

Just calculate using TIMESTAMPDIFF():

SELECT ...
FROM ...
...
ORDER BY ongoing, IF(ongoing, TIMESTAMPDIFF(NOW(), start_date), 
                              TIMESTAMPDIFF(start_date, NOW()))

Note how the order of the arguments is reversed in the 2 cases. (I may have them reversed myself - if so, just swap them in each call.)

Upvotes: 2

Related Questions