Sorting null values after all others, except special

by

Simpler:

SELECT *
FROM   tasks
ORDER  BY (sort IS NOT DISTINCT FROM -1), sort;

How?

Postgres has a proper boolean type (unlike some other RDBMS). You can order by it just like by any other data type. And it can be NULL like any other data type. Default sort order is:

FALSE (0)
TRUE (1)
NULL

(sort IS NOT DISTINCT FROM -1) evaluates to FALSE for all values except -1 – which evaluates TRUE and sorts last. Just add sort as secondary ORDER BY item.

Equivalent alternative:

SELECT *
FROM   tasks
ORDER  BY (sort IS DISTINCT FROM -1) DESC, sort;

db<>fiddle here
Old sqlfiddle

More Related Contents:

  1. Sort by column ASC, but NULL values first?
  2. Sort NULL values to the end of a table
  3. Why do NULL values come first when ordering DESC in a PostgreSQL query?
  4. Alphanumeric sorting with PostgreSQL
  5. Select rows which are not present in other table
  6. PostgreSQL DISTINCT ON with different ORDER BY
  7. ORDER BY the IN value list
  8. Create unique constraint with null columns
  9. SQL Query – Using Order By in UNION
  10. Humanized or natural number sorting of mixed word-and-number strings
  11. Optimize query with OFFSET on large table
  12. sql ORDER BY multiple values in specific order?
  13. SQL how to make null values come last when sorting ascending
  14. Best way to check for “empty or null value”
  15. How to use SQL Order By statement to sort results case insensitive?
  16. PostgreSQL ORDER BY issue – natural sort
  17. Combine two columns and add into one new column
  18. IN Clause with NULL or IS NULL
  19. Check if NULL exists in Postgres array
  20. Select first row in each GROUP BY group?
  21. How to use RETURNING with ON CONFLICT in PostgreSQL?
  22. Delete duplicate rows (don’t delete all duplicate)
  23. GROUP BY + CASE statement
  24. Return a value if no record is found
  25. Casting NULL type when updating multiple rows
  26. How to round an average to 2 decimal places in PostgreSQL?
  27. Sorting string column containing numbers in SQL?
  28. INSERT COMMAND :: ERROR: column “value” does not exist
  29. Postgresql query between date ranges
  30. Why isn’t row level security enabled for Postgres views?

Leave a Comment