13

I know that the order of returned rows is not guaranteed with the IN statement in Postgres. For example if I do this:

SELECT users.id FROM users WHERE users.id IN (13589, 16674, 13588)

I may get this result:

16674
13588
13589

However, I want returned rows to respect the order in the IN list, so I found few solutions online, such as:

SELECT users.id FROM users WHERE users.id IN (13589, 16674, 13588)
ORDER BY POSITION(id::text in '(13589, 16674, 13588)')

or

SELECT users.id FROM users WHERE users.id IN (13589, 16674, 13588)
ORDER BY id = 13589 desc,
         id = 16674 desc,
         id = 13588 desc;

I wonder if there is a nicer way to do this, or better yet more efficient?

2

2 Answers 2

12

WITH ORDINALITY in Postgres 9.4+

Introduced with Postgres 9.4. The manual:

When a function in the FROM clause is suffixed by WITH ORDINALITY, a bigint column is appended to the output which starts from 1 and increments by 1 for each row of the function's output. This is most useful in the case of set returning functions such as unnest().

SELECT u.*
FROM   unnest('{13589, 16674, 13588}'::int[]) WITH ORDINALITY AS x(id, order_nr)
JOIN   users u USING (id)
ORDER  BY x.order_nr;

array or set?

x IN (set) statements are rewritten internally in Postgres to x = ANY (array), which is equivalent:

SELECT users.id FROM users WHERE users.id = ANY ('{13589, 16674, 13588}')

You can see for yourself with EXPLAIN.

Postgres 9.3 or earlier

For now, to preserve the order of elements, you could:

SELECT u.*
FROM  (
   SELECT arr, generate_subscripts(arr, 1) AS order_nr
   FROM  (SELECT '{13589, 16674, 13588}'::int[]) t(arr)
   ) x
JOIN   users u ON u.id = x.arr[x.order_nr]
ORDER  BY x.order_nr;

db<>fiddle here

Further reading:

0
3

If you want to rewrite the condition slightly you could do something like this:

with id_list (id, sort_order) as (
   values 
      (13589, 1), 
      (16674, 2),
      (13588, 3)
)
select u.id
from users u
  join id_list il on il.id = u.id
order by il.sort_order;

You can also put the values clause directly into the join:

select u.id
from users u
  join (
   values 
      (13589, 1), 
      (16674, 2),
      (13588, 3)
  ) as il(id, sort_order) on il.id = u.id
order by il.sort_order;
2
  • 2
    @ErwinBrandstetter's method has the nice advantage that it can use an array of integers as a bind variable from a host language. Feb 22, 2014 at 16:01
  • 1
    @Colin'tHart: yes, absolutely. I just wanted to show a different way of doing this.
    – user1822
    Feb 24, 2014 at 17:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.