Retain the order while select in postgres

Question

In psql (at least in v9), if I do

select id from users where id in (2, 1, 1);

and

select id from users where id in (1, 2);

They both return the results in the same order (and eliminate dups) like: 1, 2. How do I return 2, 1, 1 instead in the first query?

score 4 · Accepted Answer · answered Jun 03 '20 at 20:42

You cannot; the order will depend on the execution plan, the physical order of the table and other things.

You could do that with an array and an explicit ORDER BY:

SELECT u.id
FROM users AS u
   JOIN unnest(ARRAY[2,2,1]) WITH ORDINALITY AS arr(elem, ord)
      ON u.id = arr.elem
ORDER BY arr.ord;

1 Answers1