Conditional sorting in MySQL?

mysql, sorting, sql

Solution

You could try `ORDER BY (done asc, aux desc)` where aux is computed with a `CASE` to yield either priority or date based on the value of `done` (you may have to cast them to the same type to fit in the same expression, e.g. cast the date to a suitable integer day number).

For example:

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

Problem

I have "tasks" table with 3 fields: - date - priority (0,1,2) - done (0,1) What I am trying to achieve is with the whole table sorted by done flag, tasks that are not done should be sorted by priority, while tasks that are done should be sorted by date: - Select * from tasks order by done asc - If done=0 additionally order by priority desc - If done=1 additionally order by date desc Is it possible to do this in MySQL without unions? Thanks.

Original source