Question 90735 in PostgreSQL Question posted in
The official documentation can be found here.

Order query results by items in WHERE clause – Postgresql

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?

Tags:

2

Answers


  1. 0

    @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);
    Login or Signup to reply.
  2. 0

    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:

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search