MySQL order by with condition

Question

There is the orders table with columns: status, created_at Status column having values: new, processing, other.

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 created_at ascending order, all other orders sorted by created_at descending order. I tried many different approaches, but stuck with each one.

For example tried combine with union two selects, but it turned out that union ignore selecting order in internal query.

Or other variant:

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;

Doesn't work too.

Answer 1

You can use this:

SELECT orders.status, orders.created_at
FROM `orders`
ORDER BY
  status='new' DESC,
  status='processing' DESC,
  CASE WHEN status IN ('new', 'processing') THEN created_at END ASC,
  CASE WHEN status NOT IN ('new', 'processing') THEN created_at END DESC

Please have a look at this fiddle. When status is in ('new', 'processing') the first case when will return the created date that will be ordered ascending, or null otherwise. When status is not in ('new', 'processing') the second case when will return the created date that will be in descending order.

Answer 2

This is working in Oracle. Please check if it works in MySQL too. Idea is to convert the created_at in desc order in case statement. multiplying -1 is not doing it. So give it a try. Also please replace sysdate to its mysql equivalent.

       select  orders.status, orders.created_at FROM orders
       ORDER BY
        CASE 
        WHEN (orders.status='new' or orders.status='processing') 
        THEN created_at end,
        case when orders.status='other' then (sysdate - created_at) 
      END;

Output

      STATUS        CREATED_AT          
      processing    20-AUG-2015 22:11:24
      processing    30-AUG-2015 22:11:24
      new           30-AUG-2015 22:11:24
      new           09-SEP-2015 22:11:24
      other         10-AUG-2015 22:11:24
      other         31-JUL-2015 22:11:24