Postgresql Custom Sorting

Question

I want to sort by a column where I want numbers to be ASC, but the last 2 rows should always be 0 then 1 (if they exists)

For example:

• 8
• 6
• 10
• 0
• 2
• 1

becomes

• 2
• 6
• 8
• 10
• 0
• 1

How would this be possible?

Answer 1

I think the clearest is to order both by

CASE WHEN column_name = 0
     THEN 0
     WHEN column_name = 1
     THEN 1
     ELSE -1
 END

(which puts all values except 0 and 1 before 0, and 0 before 1) and by column_name (so that the non-0, non-1 values appear in order).

For example:

SELECT column_name
  FROM table_name
 ORDER
    BY CASE WHEN column_name = 0
            THEN 0
            WHEN column_name = 1
            THEN 1
            ELSE -1
        END,
       column_name
;

Answer 2

SELECT * 
FROM YOURTABLE
ORDER BY CASE YOURCOLUMN
         WHEN YOURCOLUMN=0 THEN 0
         WHEN YOURCOLUMN=1 THEN 1
         ELSE -99
         END;