Sort null values after all other but special values
I have a PostgreSQL article table with an optional order field:
CREATE TABLE tasks (
id integer PRIMARY KEY DEFAULT nextval('f_seq'),
f_id integer REFERENCES fixins,
name text NOT NULL,
sort integer
);
I want tasks that don't have a value sort
to sort after all the others, with one exception: if sort = -1
I want it to be sorted after them. So, for example, given these values:
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 |
I want them to be sorted as: alpha
, beta
, gamma
, delta
, epsilon
, zeta
.
I know that I can use ORDER BY COALESCE(sort,99999)
to order the null values after the non-null values, but how can I get that special value -1
that follows after that?
source to share
Simpler:
SELECT *
FROM tasks
ORDER BY (sort IS NOT DISTINCT FROM -1), sort;
How?
Postgres is of the correct boolean
type (unlike some other RDBMS). You can order by it as you would any other data type. And it can be NULL, just like any other data type. The default sort order is:
FALSE (0)
TRUE (1)
NULL
(sort IS NOT DISTINCT FROM -1)
evaluates as FALSE
for all values except -1
- which evaluates TRUE
and sorts last. Just add sort
as secondary ORDER BY
.
Equivalent Alternative:
SELECT *
FROM tasks
ORDER BY (sort IS DISTINCT FROM -1) DESC, sort;
source to share