# Postgresql – Order of returned rows with IN statement

order-bypostgresql

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?

## 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