CODEWITHSUNDEEP
sqlpostgresqlsql-order-by
alex.bour
alex.bour

Reputation: 2964

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

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

Upvotes: 1

Views: 45

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520898

You could order by a CASE expression:

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

Upvotes: 0

GMB
GMB

Reputation: 222402

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

Upvotes: 1

Related Questions