Sort NULL values to the end of a table

sql postgresql null sql-order-by
helle picture helle · Oct 1, 2011 · Viewed 34.7k times · Source

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

Answer

Erwin Brandstetter picture Erwin Brandstetter · Oct 1, 2011

First of all, NULL values are sorted last in default ascending order. You don't have to do anything extra.

The issue applies to descending order, which is the perfect inverse and thus sorts NULL values first. The solution @Mosty pointed out was introduced with PostgreSQL 8.3:

ORDER BY somevalue DESC NULLS LAST

For PostgreSQL 8.2 and older or other RDBMS without this standard SQL feature you can substitute:

ORDER BY (somevalue IS NULL), somevalue DESC

FALSE sorts before TRUE, so NULL values come last, just like in the example above.

Related later answer:

Related questions

Sort by column ASC, but NULL values first?

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_…

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