How to Sort PostgreSQL Data with Nulls and a Special Case (-1) Value?

Sorting Values with Special Cases in PostgreSQL

This discussion centers around arranging data from a PostgreSQL table named "tasks." Each task has an optional field called "sort." The goal is to prioritize sorting tasks without "sort" values after all others, with the exception of those with "-1" as their sort value, which should appear after all others.

Consider the following 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
6	1	epsilon

Using COALESCE(sort,99999) alone will group null values after non-null values, but it will not address the special case of "-1."

The solution employs PostgreSQL's boolean data type to achieve the desired sorting order:

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

This query takes advantage of the fact that the comparison "(sort IS NOT DISTINCT FROM -1)" evaluates to FALSE for all values except "-1," which itself evaluates to TRUE. By sorting by this expression first, the "-1" values are placed at the end of the order, regardless of their sort values.

An alternative approach, using the DESC keyword, is also presented:

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

Both methods effectively sort tasks without "sort" values after all others, while ensuring that "-1" values appear after all other non-null sort values.

The above is the detailed content of How to Sort PostgreSQL Data with Nulls and a Special Case (-1) Value?. For more information, please follow other related articles on the PHP Chinese website!