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?

+1


source to share


2 answers


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;

      

SQL Fiddle.

+2


source


SELECT name FROM tasks
  WHERE f_id=1
  ORDER BY
    CASE COALESCE(sort,88888)
      WHEN -1 THEN 99999
      ELSE         COALESCE(sort,88888)
    END,
    id;

      



Look for a more elegant way to do it just once without choosing it.

0


source







All Articles