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?

ruakh · Accepted Answer · 2012-04-01 20:40:16Z

11

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
;

answered Apr 1, 2012 at 20:40

ruakh

185k29 gold badges292 silver badges324 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Teja · Accepted Answer · 2012-04-01 20:43:03Z

1

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

answered Apr 1, 2012 at 20:43

Teja

13.7k38 gold badges103 silver badges164 bronze badges

Collectives™ on Stack Overflow

Postgresql Custom Sorting

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related