130

Is there a way with PostgreSQL to sort rows with NULL values in fields to the end of the selected table?

Like:

SELECT * FROM table ORDER BY somevalue, PUT_NULL_TO_END
helle
  • 11,183
  • 9
  • 56
  • 83

2 Answers2

253

NULL values are sorted last in default ascending order. You don't have to do anything extra.

The issue applies to descending order, which is the perfect inverse and thus sorts NULL values on top.
PostgreSQL 8.3 introduced NULLS LAST:

ORDER BY somevalue DESC NULLS LAST

For PostgreSQL 8.2 and older or other RDBMS without this standard SQL feature:

ORDER BY (somevalue IS NULL), somevalue DESC

FALSE sorts before TRUE, so NULL values come last, just like in the example above.

See:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 3
    IMHO in most real world applications you would want null values last whatever the order. For example sorting DESC on an optional timestamp, firstname, lastname, ... so I find it really suspect even though it seems to make sense that mathematically DESC order is the opposite of ASC. Maybe the nulls are just to be in a category of their own and should not be affected by ASC, DESC and always put last, that would have been a better default. – Christophe Roussy Oct 24 '18 at 16:38
  • It could affect the indexes if they are DESC maybe you could add a note about that too ? https://www.postgresql.org/message-id/AANLkTinvi12K%3DcK50nLiFSsCGUq3Ven0tLRW_6w2H-3E%40mail.gmail.com – Christophe Roussy Oct 24 '18 at 16:48
  • @ChristopheRoussy: Indexes matching the sort order are touched in the linked answer above. – Erwin Brandstetter Oct 24 '18 at 17:24
  • 4
    Personally I think the default should have been the opposite: NULLs come first in ascending order, and last in descending order. That's much more intuitive, as NULL is the "smallest" value. – Stephen May 24 '19 at 14:04
  • 1
    For people coming from MYSQL , this is a lol? Why was it built like this in the first place. OR did Mysql over optimize the result – CodeGuru Nov 19 '19 at 13:21
  • @ChristopheRoussy: IMHO in the real world there are more much more uses cases where NULL should be the "smallest" value. So, this default implementation is kind of weird like a lot of other things in postgres. – QStorm Nov 29 '22 at 10:25
66

Does this make the trick?

ORDER BY somevalue DESC NULLS LAST

Taken from: http://www.postgresql.org/docs/9.0/static/sql-select.html

Mosty Mostacho
  • 42,742
  • 16
  • 96
  • 123
  • The accepted solution works but I prefer this better for a few reasons: 1) It's non-intuitive (to me at least) that `DESC` prioritizes `null` in the first place, 2) This command is actually obvious to what it's going to do with the result – Matheus Felipe Nov 23 '22 at 18:17