1

I have a PostgreSQL table of items with an optional ordering 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 have no sort value to sort after all others, with one exception: if sort = -1 I want it to sort after those. 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 sort as: alpha, beta, gamma, delta, epsilon, zeta.

I know that I can use ORDER BY COALESCE(sort,99999) to order null values after non-nulls, but how can I get that special -1 value to come after those?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Phrogz
  • 296,393
  • 112
  • 651
  • 745

2 Answers2

2

Simpler:

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

How?

Postgres has a proper boolean type (unlike some other RDBMS). You can order by it just like by any other data type. And it can be NULL like any other data type. Default sort order is:

FALSE (0)
TRUE (1)
NULL

(sort IS NOT DISTINCT FROM -1) evaluates to FALSE for all values except -1 - which evaluates TRUE and sorts last. Just add sort as secondary ORDER BY item.

Equivalent alternative:

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

db<>fiddle here
Old sqlfiddle

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
0
SELECT name FROM tasks
  WHERE f_id=1
  ORDER BY
    CASE COALESCE(sort,88888)
      WHEN -1 THEN 99999
      ELSE         COALESCE(sort,88888)
    END,
    id;

Still looking for a more elegant way to only do that coalesce once, without selecting it.

Phrogz
  • 296,393
  • 112
  • 651
  • 745