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

by

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.

More Related Contents:

  1. Sort by column ASC, but NULL values first?
  2. Best way to check for “empty or null value”
  3. Sorting null values after all others, except special
  4. Sort NULL values to the end of a table
  5. Select rows which are not present in other table
  6. Refactor a PL/pgSQL function to return the output of various SELECT queries
  7. How to implement a many-to-many relationship in PostgreSQL?
  8. Create unique constraint with null columns
  9. Optimise PostgreSQL for fast testing
  10. Transpose latest rows per user to columns
  11. How to delete duplicate rows without unique identifier
  12. sql ORDER BY multiple values in specific order?
  13. Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?
  14. PostgreSQL parameterized Order By / Limit in table function
  15. why is null not equal to null false
  16. NOT NULL constraint over a set of columns
  17. PostgreSQL ORDER BY issue – natural sort
  18. How do I alter the position of a column in a PostgreSQL database table?
  19. Alphanumeric sorting with PostgreSQL
  20. Continuing a transaction after primary key violation error
  21. Use email address as primary key?
  22. IN Clause with NULL or IS NULL
  23. SQL query to find record with ID not in another table
  24. How to show row numbers in PostgreSQL query?
  25. How to compare dates in datetime fields in Postgresql?
  26. PostgreSQL how to create a copy of a database or schema?
  27. Space used by nulls in database
  28. Temporal database design, with a twist (live vs draft rows)
  29. PostgreSQL nested INSERTs / WITHs for foreign key insertions
  30. Postgres: convert single row to multiple rows (unpivot)

Leave a Comment