Choosing MySQL with multiple orders

Question

So, I got this table:

  + ---- + --------------------- + ----- + ---------------- +
 |  ID |  WHEN |  OFF |  (other..stuff) |
 + ---- + --------------------- + ----- + ---------------- +
 |  1 |  2012-09-17 17:00:00 |  0 |  anything1 |
 |  2 |  2012-09-17 18:00:00 |  0 |  anything2 |
 |  3 |  2012-08-31 21:00:00 |  1 |  blabla321 |
 |  4 |  2012-08-31 18:30:00 |  1 |  blab32121 |
 + ---- + --------------------- + ----- + ---------------- +

I want to select all the keys, but:

• Keys with disabled = 0 must be of order when ASC, and they must be displayed first
• Keys with disabled = 1 should be of order when DESC, and they should be displayed after (at the end)

I tried something like this:

(SELECT * FROM `table` WHERE `off` = 0 ORDER BY `when` ASC) UNION (SELECT * FROM `table` WHERE `off` = 1 ORDER BY `when` DESC) 

But that will not work.

Also check: http://i.imgur.com/81Hzq.jpg

Answer 1

 select * from `table` order by `off`, case `off` when 0 then timestampdiff(second, current_timestamp, `when`) when 1 then timestampdiff(second, `when`, current_timestamp) end 

Answer 2

Try the following:

 (SELECT * FROM table WHERE off = 0 ORDER BY `when` ASC) UNION (SELECT * FROM table WHERE off = 1 ORDER BY `when` DESC); 

I think WHEN is a keyword and should be specified.

Answer 3

 SELECT * FROM table ORDER BY CASE WHEN off = 0 THEN `when` END ASC, CASE WHEN off = 1 THEN `when` END DESC