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

How to sort by order of values in a select statement same as values "in" clause for postgresql

I want to use a SELECT query with an IN clause, as well as Order by value same as IN clause

Query:

select * 
from table 
where column_xyz IN ('124','225','111') // Here values must be in single quotes in query


Output needs to be fetched : 124,225,111

Tried query :

  select * 
    from table 
    where column_xyz IN ('124','225','111') order by field(column_xyz,'124','225','111')

In mysql we have option called ‘FIELD’ but for postgres I am unable to work with ‘FIELD’ i.e it didn’t work

Tags:

2

Answers


  1. 0 
    select t.* 
from the_table t
inner join
 unnest(array[124,225,111]::integer[]) with ordinality as a(v, o)
 on column_xyz = v
order by o;
    Login or Signup to reply.
  2. 0

    As you pointed out, Postgres does not directly support any function like MySQL’s FIELD() function. The canonical way to do this in ANSI SQL uses a CASE expression in the ORDER BY clause:

    SELECT * 
FROM yourTable
WHERE xyz IN ('124', '225', '111')
ORDER BY CASE xyz WHEN '124' THEN 1 WHEN '225' THEN 2 WHEN '111' THEN 3 END;

    In case you actually need to sort many more than 3 possible xyz values, then you should maintain a table which maps each xyz value to its sort order:

    table: xyz_ord

xyz | ord
124 | 1
225 | 2
111 | 3

    Then, join to this table and use the ord column to sort your original query:

    SELECT t1.* 
FROM yourTable t1
INNER JOIN xyz_ord t2
    ON t2.xyz = t1.xyz
WHERE t1.xyz IN ('124', '225', '111')
ORDER BY t2.ord;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search