CODEWITHSUNDEEP
sqlpostgresql
Roko
Roko

Reputation: 1325

Sort by specific order, including NULL, postgresql

best explained with an example:

So I have users table:

id   name   product
1   second    NULL
2   first     27
3   first     27
4   last      6
5   second    NULL

And I would like to order them in this product order: [27,NULL, 6] So I will get:

id   name   product
2   first     27
3   first     27
1   second    NULL
5   second    NULL
4   last      6

(notice user id 3 can be before user id 2 since they both have the same product value)

Now without NULL I could do it like that: 

SELECT id FROM users ORDER BY users.product=27, users.product=6;

How can I do it with NULL ?

p.s. I would like to do that for many records so it should be efficient.

Upvotes: 1

Views: 66

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269463

As a note, you can follow your original approach. You just need a NULL-safe comparison:

SELECT id
FROM users
ORDER BY (NOT users.product IS DISTINCT FROM 27)::int DESC,
         (user.product IS NULL)::int DESC,
         (NOT users.product IS DISTINCT FROM 6)::int DESC;

The reason your version has unexpected results is because the first comparison can return NULL, which is ordered separately from the "true" and "false".

Upvotes: 1

Gurwinder Singh
Gurwinder Singh

Reputation: 39457

You can use case to produce custom sort order:

select id
from users
order by case 
        when product = 27
            then 1
        when product is null
            then 2
        when product = 6
            then 3
        end

Upvotes: 2

Related Questions