How to Sort Non-Null Values Ascending, then Nulls, with -1 Values Last in PostgreSQL?

Sorting Null Values in a Special Manner after Non-Null Values

Background

In a PostgreSQL table with an optional sort field, the goal is to sort values as follows:

• Non-null values should come first, sorting in ascending order based on their sort values.
• NULL values should come after non-null values.
• Exceptionally, sort values of -1 should appear after all other non-null values.

Solution

To achieve this sorting behavior, one approach is to use the following ORDER BY clause:

ORDER BY (sort IS NOT DISTINCT FROM -1), sort

Explanation

This clause operates as follows:

• (sort IS NOT DISTINCT FROM -1) evaluates to FALSE for all values except -1, which evaluates to TRUE.
• The expression is sorted in ascending order, meaning FALSE values (non-null values other than -1) come before TRUE values (-1).
• The secondary ORDER BY clause then sorts by the sort value, ensuring that non-null values are sorted in ascending order within their respective groups.

Example

Consider the provided sample data:

id	f_id	name	sort
1	1	zeta	-1
2	1	alpha	1
3	1	gamma	3
4	1	beta	2
5	1	delta	NULL
6	1	epsilon	NULL

Using the provided clause, the items would be sorted as follows:

alpha, beta, gamma, delta, epsilon, zeta

Alternative Forms

An equivalent alternative to the initial clause is:

ORDER BY (sort IS DISTINCT FROM -1) DESC, sort

This clause produces the same sorting behavior but uses the DESC keyword to sort the (sort IS DISTINCT FROM -1) expression in descending order.

The above is the detailed content of How to Sort Non-Null Values Ascending, then Nulls, with -1 Values Last in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!