Sort by NULL field only if field is NULL, then sort by name in Postgresql

Question

I have the following sort in Postgresql:

ORDER BY verified_at NULLS FIRST, name ASC

What I want is having all records with verified_at NULLS first, but only if the field verified_at is not null, then order by name. I don't want the verified_at to have the priority on name if verified_at IS NOT NULL.

With this code, verified_at has the priority on name, after the NULLs are displayed. In fact, is-it possible to have records order by name except for the ones that have verified_at NULL ?

Example of what I want:

verified_at:     name:
NULL             Alex
NULL             Bernard
NULL             Toto
2018             Albert
2012             Boris
2015             Michel
2001             Lena

GMB · Accepted Answer · 2020-03-26 17:18:21Z

1

You could add a first sorting criteria that checks if the data is null, then order by name:

order by (verified_at is not null)::int, name

Actually using the boolean directly should also work:

order by verified_at is not null, name

answered Mar 26, 2020 at 17:18

GMB

224k25 gold badges103 silver badges151 bronze badges

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

Comments

Tim Biegeleisen · Accepted Answer · 2020-03-26 17:19:23Z

0

You could order by a CASE expression:

ORDER BY
    CASE WHEN verified_at IS NULL THEN 0 ELSE 1 END,
    name

answered Mar 26, 2020 at 17:19

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

Collectives™ on Stack Overflow

Sort by NULL field only if field is NULL, then sort by name in Postgresql

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