1

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?

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
Chris Kao
  • 410
  • 4
  • 12

2 Answers2

3

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:

Erwin Brandstetter
  • 605,456
  • 145
  • 1,078
  • 1,228
1

@chris Kao, use Position in postgresql.

Approach : 1 SELECT id, name FROM my_table WHERE id in (1,3,5,2,4,6) order by position(id::text in '1,3,5,2,4,6') output:

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

Aprroach : 2

select id, name
from my_table mt
where id in (1,3,5,2,4,6)
order by array_position(array[1,3,5,2,4,6], mt.id);
Learn Hadoop
  • 2,760
  • 8
  • 28
  • 60