Custom sorting of a query The application I am working on displays some tasks based on status, and date (in that order). However, for a particular status, the sort condition should be inverted. For example, a table which looks like this :

 id    | status   | planned_date
-------+----------+--------------
 1     | inactive | 2015-03-12
 2     | active   | 2015-03-13
 3     | inactive | 2015-03-13
 4     | inactive | 2015-03-14
 5     | active   | 2015-03-12
 6     | active   | 2015-03-16
 7     | active   | 2015-03-17


Should be returned as

 id    | status   | planned_date
-------+----------+--------------
 5     | active   | 2015-03-12
 2     | active   | 2015-03-13
 6     | active   | 2015-03-16
 7     | active   | 2015-03-17
 4     | inactive | 2015-03-14
 3     | inactive | 2015-03-13
 1     | inactive | 2015-03-12


Note: For this example to be simple, only two statuses are used, but this is an enumeration of 6 different values for this field; new, pending, active, inactive, cancelled, completed. Also, there are other fields that need to be sorted: planned_date, and priority (a numeric value from -3 to 3, 0 being "normal"). I am mentioning this only as a FYI, as I don't believe it to be much relevant, and that the general idea can be understood with the simple example above.

To sort by the status field (an enum) I simply perform

SELECT id, status, planned_date
FROM tbl_tasks
WHERE ...
ORDER BY status ASC;


Now, how do I sort the planned_date field (ASC for active status, and DESC for inactive status)?
You could use CASE WHEN operator for solving this:

ORDER BY status,
    CASE status
        WHEN 'active' THEN planned_date - now()
        WHEN 'inactive' THEN now() - planned_date
    END