I am trying to do some ordering on a mysql query that I can't figure out.
id | status | created_at ------------------------ 1 | open | 1348778070 2 | closed | 1348711241 3 | open | 1348839204 4 | closed | 1348738073 5 | banned | 1348238422
How do I order the above table so that the 'open' records are first, in ASC order; and then the non-open records are second in DESC order? In another word, have a dynamic second level ordering direction based on some condition?
I have tried a UNION of two SELECT queries with ordering within them, which doesn't work because UNION by default produces an unordered set of rows.
Also I've tried a pseudo column that subtracts the created_at timestamp from a large number, for the closed status records, so I can just ORDER BY ASC to get the result as per 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
This works but I feel there has to be a better way. Ideally a solution would not include a random big number as above
SELECT * FROM tmp_order ORDER BY FIELD(status, 'open') DESC, CASE WHEN status = 'open' THEN created_at ELSE (999999999 - created_at) END
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
| ID | STATUS | CREATED_AT | ---------------------------- | 1 | open | 1348778070 | | 3 | open | 1348839204 | | 4 | closed | 1348738073 | | 2 | closed | 1348711241 | | 5 | banned | 1348238422 |
Here is SQLFiddle demo.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!Donate Us With