How to Sort Nulls Last and a Specific Value Second to Last in PostgreSQL?

How to Sort null Values After All Others, with Special Case

Problem:

When sorting a PostgreSQL table with an optional sorting field (sort), how can you ensure that:

• Null values in sort sort after all non-null values.
• A special value of -1 in sort sorts after regular non-null values but before null values.

Solution:

Use the following SQL statement:

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

Explanation:

• The expression (sort IS NOT DISTINCT FROM -1) evaluates to:

  • FALSE for all values of sort except -1.
  • TRUE for sort = -1.
• Ordering by this expression first groups values into two categories: sort = -1 and all other sort values.
• The secondary ordering by sort orders within each category.

Example:

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  |     

The query will produce the following sorted result:

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

The above is the detailed content of How to Sort Nulls Last and a Specific Value Second to Last in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!