Conditional sorting in MySQL?

Question

I have a “task” table with three fields:

• the date
• priority (0,1,2)
• done (0,1)

What I'm trying to achieve is that the whole table sorted by the set flag, tasks that are not executed should be sorted by priority, and tasks that are executed should be sorted by date:

• Select * from the order of tasks by running asc
• If done = 0 additionally prioritize desc
• If done = 1 additionally sort by date desc

Is it possible to do this in MySQL without unions?

Thanks.

Answer 1

You can try ORDER BY (done asc, aux desc) , where aux is computed using CASE to get either a priority or a date based on the done value (you may have to refer them to the same type to match a single expression, e.g. , indicate the date on a suitable integer day).

For instance:

 SELECT * FROM tab ORDER BY done desc, case done when 0 then prio else to_days(thedate) end desc; 

Answer 2

Taken from Alex Martelli, slightly reduced with IF () and fixed ASC / DESC ordering

 SELECT * FROM tab ORDER BY done ASC, IF(done, to_days(thedate), prio) DESC;