Search code examples
sqlpostgresqlsql-order-by

Order query results by items in WHERE clause

How to let the query result be ordered by the exact order of passed items in the WHERE clause?

For example, using this query:

SELECT id, name FROM my_table
WHERE id in (1,3,5,2,4,6)
ORDER BY id

Result:

id | name
---------
1  |  a
2  |  b
3  |  c
4  |  d
5  |  e
6  |  f

What I expected:

id | name
---------
1  |  a
3  |  c
5  |  e
2  |  b
4  |  d
6  |  f

I noticed that there is a FIELD() function in MySQL. Is there an equivalent function in PostgreSQL?

Solution

  • Pass an array and use WITH ORDINALITY. That's cleanest and fastest:

    SELECT id, t.name
FROM   unnest ('{1,3,5,2,4,6}'::int[]) WITH ORDINALITY u(id, ord)
JOIN   my_table t USING (id)
ORDER  BY u.ord;

    Assuming values in the passed array are distinct. Else, this solution preserves duplicates, while IN removes them. You'd have to define which behavior you want. But then the desired sort order is also ambiguous, which would make the question moot.

    See: