Why do NULL values come first when ordering DESC in a PostgreSQL query?

9
votes

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.

2
sqldatabasepostgresqlnullsql-order-by
Supposedly the SQL standard doesn't specify how NULL should be ordered, so its up to the people that wrote it into Postgres: en.wikipedia.org/wiki/Order_by - Sam
Why have you wanted nulls first? - Bryan
@usr Can you explain how the question "Why do NULL values come first when ordering DESC in a PostgreSQL query?" is opinion based? - Bryan
@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
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

2 Answers

17
votes

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.

1
votes

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.