Different ORDER BY direction for MySql query results

Question

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

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 |

Here is SQLFiddle demo.