title: MySQL order by with condition
body: <p>There is the orders table with columns: <code>status</code>, <code>created_at</code>
  Status column having values: new, processing, other.</p>
  
  <p>The issue is to select all orders, first of all with status new after that with status processing, after that all others.
  New and processing orders should be sorted by <code>created_at</code> ascending order, all other orders sorted by <code>created_at</code>  descending order.
  I tried many different approaches, but stuck with each one.</p>
  
  <p>For example tried combine with union two selects, but it turned out that union ignore selecting order in internal query.</p>
  
  <p>Or other variant:</p>
  
  <pre><code>SELECT orders.status, orders.created_at FROM `orders`  ORDER BY status =     'new' DESC, status = 'processing' DESC,
  CASE 
    WHEN (orders.status='new' or orders.status='processing') 
    THEN -created_at 
    ELSE created_at
  END;
  </code></pre>
  
  <p>Doesn't work too.</p>