# Order of returned rows with IN statement

## All we need is an easy explanation of the problem, so here it is.

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?

## How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

### Method 1

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

### Method 2

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;
``````

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0