Different ORDER BY directions for MySql query results

Question

I am trying to do some order in a mysql query that I cannot understand.

id | status | created_at ------------------------ 1 | open | 1348778070 2 | closed | 1348711241 3 | open | 1348839204 4 | closed | 1348738073 5 | banned | 1348238422 

How to arrange the above table so that first open records are opened, in ASC order; and then non-open records are second in DESC order? In other words, do you have a dynamic second-level ordering direction based on some condition?

I tried UNION from two SELECT queries with ordering inside them, which does not work, because UNION creates an unordered rowset by default.

I also tried a pseudo-column that subtracts the created_at timestamp from a large number for private status records, so I can just ORDER BY ASC to get the result as below ...

 SELECT table.*, (table.created_at) as tmp_order FROM table WHERE table.status = 'open' UNION SELECT table.*, (999999999 - table.created_at) as tmp_order FROM table WHERE table.status = 'closed' ORDER BY tmp_order ASC 

It works, but I feel that there should be a better way. Ideally, the solution would not include a random large number, as indicated above

Answer 1

UPDATED

 SELECT * FROM tmp_order ORDER BY FIELD(status, 'open') DESC, CASE WHEN status = 'open' THEN created_at ELSE (999999999 - created_at) END 

or

 SELECT * FROM tmp_order ORDER BY FIELD(status, 'open') DESC, CASE WHEN status = 'open' THEN created_at END, CASE WHEN status <> 'open' THEN created_at END DESC 

Output:

  |  ID |  STATUS |  CREATED_AT |
 ----------------------------
 |  1 |  open |  1348778070 |
 |  3 |  open |  1348839204 |
 |  4 |  closed |  1348738073 |
 |  2 |  closed |  1348711241 |
 |  5 |  banned |  1348238422 |

Below is the SQLFiddle .

Answer 2

Try:

 SELECT id, status, if (status = 'open', created_at, 999999999 - created_at) as tmp_order FROM table ORDER BY status, tmp_order 

Answer 3

Here is how I would decide:

 SELECT id, status, created_at FROM yourtable ORDER BY status DESC, CASE WHEN status='open' THEN created_at END, CASE WHEN status='closed' THEN created_at END DESC 

Answer 4

In your case, you can do this in one request, but a general solution that works for any two different and unrelated. Orders - use two combined subqueries, each with its own order;

 SELECT * FROM ( SELECT * FROM table WHERE table.status = 'open' ORDER BY created_at DESC) x UNION ALL SELECT * FROM ( SELECT * FROM table WHERE table.status = 'closed' ORDER BY created_at) y