8

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?

  • "Nicer" is quite subjective; would you consider using ORDER BY CASE WHEN ... THEN ... as nicer ? As to efficiency, I'm pretty sure optimizer will generate the same plan. – a1ex07 Feb 22 '14 at 5:18
7

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:

| improve this answer | |
  • Oh that's a sweet construct – Tom V Sep 16 at 19:05
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;
| improve this answer | |
  • 2
    @ErwinBrandstetter's method has the nice advantage that it can use an array of integers as a bind variable from a host language. – Colin 't Hart Feb 22 '14 at 16:01
  • @Colin'tHart: yes, absolutely. I just wanted to show a different way of doing this. – a_horse_with_no_name Feb 24 '14 at 17:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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