Sort by column ASC, but NULL values first?

sql postgresql null sql-order-by
mhd picture mhd · Mar 1, 2012 · Viewed 45.7k times · Source

I need to sort a PostgreSQL table ascending by a date/time field, e.g. last_updated.

But that field is allowed to be empty or null and I want records with null in last_updated come before non-null last_updated.
Is this possible?

order by last_updated asc  -- and null last_updated records first ??

Answer

Erwin Brandstetter picture Erwin Brandstetter · Mar 1, 2012

Postgres provides the NULLS FIRST | LAST keywords for the ORDER BY clause to cater for that need exactly:

... ORDER BY last_updated NULLS FIRST

A typical use case is with descending sort order (DESC), which yields the complete inversion of the default ascending order (ASC) with null values first. Often not desirable - so, to keep null values last:

... ORDER BY last_updated DESC NULLS LAST

To support the query with an index, make it match:

CREATE INDEX foo_idx ON tbl (last_updated DESC NULLS LAST);

Postgres can read btree indexes backwards, but it matters where NULL values are appended.

Related questions

Sort NULL values to the end of a table

Is there a way with PostgreSQL to sort rows with NULL values in fields to the end of the selected table? Like: SELECT * FROM table ORDER BY somevalue, PUT_NULL_TO_END

Read More
Best way to check for "empty or null value"

What is best way to check if value is null or empty string in Postgres sql statements? Value can be long expression so it is preferable that it is written only once in check. Currently I'm using: coalesce( trim(stringexpression),…

Read More
Select rows which are not present in other table

I've got two postgresql tables: table name column names ----------- ------------------------ login_log ip | etc. ip_location ip | location | hostname | etc. I want to get every IP address from login_log which doesn't have a row in ip_location. I …

Read More