Custom sorting of a query

Question

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)?

Answer 1

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

Answer 2

Another way is to use ROW_NUMBER():

select *
from t
order by status,
         case status 
             when 'active'   then  1 
             when 'inactive' then -1
         end 
         * row_number() over (partition by status order by planned_date) ; 

Test at SQLfiddle

Answer 3

Accidentally read this as a SQL Server question, for Postgres it would have to be a materialized view to do the equivalent. I'll re-use Simo's logic for the sake of consistency.

CREATE MATERIALIZED VIEW view AS
  SELECT
      id, status,
      CASE status_date
         WHEN 'active' THEN planned_date - now()
         WHEN 'inactive' THEN now() - planned_date
      END
    FROM table
    WHERE ...
    ORDER BY status, status_date