14

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.

Improve this question
5
  • 1
    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 Commented Jan 6, 2014 at 20:32
  • Why have you wanted nulls first? Commented Jan 6, 2014 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? Commented Jan 6, 2014 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" :) ). Commented Jan 7, 2014 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". Commented Jan 7, 2014 at 18:00

2 Answers 2

Reset to default
25

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 you can choose which way you want it. See:

The manual:

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.

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

2 Comments

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: 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.
2

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.

Improve this answer

1 Comment

You are quoting the manual for the very old and unsupported version 8.2.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.