How to Sort NULL Values to the End of a PostgreSQL Table?

Sorting NULL Values to the End of a Table in PostgreSQL

When sorting data, NULL values can present a challenge as they can disrupt the intended order. In PostgreSQL, there are different behaviors for NULL values based on the sort order used.

Default Ascending Order

By default, when sorting rows in ascending order, NULL values are placed at the beginning of the result. This is because NULL is considered to be less than any non-NULL value.

Descending Order without NULLS LAST

However, when sorting in descending order without using the NULLS LAST option, NULL values are placed at the top. This is because NULL is considered to be greater than any non-NULL value in descending order.

Descending Order with NULLS LAST

To sort NULL values to the end of the table in descending order, use the NULLS LAST option:

ORDER BY somevalue DESC NULLS LAST

This option specifies that NULL values should be treated as the lowest values when sorting in descending order.

Alternative Method for PostgreSQL 8.2 and Older

For PostgreSQL versions 8.2 and older, or for RDBMS that do not support the NULLS LAST option, you can use the following workaround:

ORDER BY (somevalue IS NULL), somevalue DESC

This expression utilizes the fact that FALSE (which represents NULL) sorts before TRUE (which represents non-NULL values). By checking if the somevalue field is NULL, we effectively move NULL values to the end of the sort order.

The above is the detailed content of How to Sort NULL Values to the End of a PostgreSQL Table?. For more information, please follow other related articles on the PHP Chinese website!