4

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

6

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
5

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

  • Wow... :) Is this faster than the Simo's answer? – Yanick Rochon Mar 18 '15 at 18:42
2

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
  • Can this be used on a view as well? – Yanick Rochon Mar 18 '15 at 21:02
  • Thought this was for SQL Server, so I've updated it as a view that will work for Postgres. – LowlyDBA Mar 18 '15 at 21:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.