12

When would you ever want NULLS first when ordering a query descending or ascending?

In my opinion, the vast majority of the time the desired behavior whether sorting ascending or descending would be NULLS LAST. Instead, we should have to specify NULLS FIRST.

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Bryan
  • 17,201
  • 24
  • 97
  • 123
  • 1
    Supposedly the SQL standard doesn't specify how `NULL` should be ordered, so its up to the people that wrote it into Postgres: http://en.wikipedia.org/wiki/Order_by – Sam Jan 06 '14 at 20:32
  • Why have you wanted nulls first? – Bryan Jan 06 '14 at 20:45
  • @usr Can you explain how the question "Why do NULL values come first when ordering DESC in a PostgreSQL query?" is opinion based? – Bryan Jan 06 '14 at 22:53
  • @Bryan I don't think it is universal that NULLs are always needed first or last. If NULLs are an important anomaly or missing data, you might like to show them to the use first. If they represent unimportant cases you want to show them last. It totally depends on the use case. (Funnily, your question contains the phrase "In my opinion" :) ). – usr Jan 07 '14 at 15:40
  • If nulls are an important anomaly - sounds like an edge case. My "description of the problem" contains "In my opinion", but the question does not. While my description is opinion based, the question is not an "Opinion-based question". – Bryan Jan 07 '14 at 18:00

2 Answers2

21

Actually, with default sort order (ASCENDING) NULL values come last.

Logic dictates that the sort order be reversed with the DESCENDING keyword, so NULLs come first in this case.

But the best part comes last: you can choose which way you want it:

Quoting the current manual, version 9.3 as of writing:

If NULLS LAST is specified, null values sort after all non-null values; if NULLS FIRST is specified, null values sort before all non-null values. If neither is specified, the default behavior is NULLS LAST when ASC is specified or implied, and NULLS FIRST when DESC is specified (thus, the default is to act as though nulls are larger than non-nulls). When USING is specified, the default nulls ordering depends on whether the operator is a less-than or greater-than operator.

Bold emphasis mine.

Community
  • 1
  • 1
Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
  • 4
    Yes, it is logical that descending should be the opposite of ascending. At the same time, NULL values have "no value", therefore NULL values should never be at the top of any ordered list. – Bryan Jan 07 '14 at 00:34
  • 1
    @Bryan: depends on what you're doing. Sometimes, it makes sense for them to go first. The real issue is how and where to place the null data in a btree index. In postgres, the decision was made to put them last in there, so a reversed index look up makes them show up first. – Denis de Bernardy Jan 07 '14 at 01:44
1

The simple answer is because that's how the people who wrote Postgres designed it. To quote:

The null value sorts higher than any other value. In other words, with ascending sort order, null values sort at the end, and with descending sort order, null values sort at the beginning.

This assumes that you have specified an ORDER BY clause, if you haven't then the rows are returned randomly.

If the ORDER BY clause is specified, the returned rows are sorted in the specified order. If ORDER BY is not given, the rows are returned in whatever order the system finds fastest to produce.

Ben
  • 51,770
  • 36
  • 127
  • 149