Search code examples
sqlpostgresqlsql-order-bysql-in

PostgreSQL ORDER BY values in IN() clause

Ok, there are some answers out there on how to do this. But all of the answers are assuming that the query is selecting all. If you have a distinct select, the methods no longer work.

See here for that method: Simulating MySQL's ORDER BY FIELD() in Postgresql

Basically I have

SELECT DISTINCT id 
FROM items 
WHERE id IN (5,2,9) 
ORDER BY
 CASE id
  WHEN 5 THEN 1 
  WHEN 2 THEN 2
  WHEN 9 THEN 3
 END

Of course, this breaks and says

"PGError: ERROR: for SELECT DISTINCT, ORDER BY expressions must appear in select list"

Is there any way to order your query results in PostgreSQL by the order of the values in the IN clause?

Solution

  • You can wrap it into a derived table:

    SELECT *
FROM (
  SELECT DISTINCT id 
  FROM items 
  WHERE id IN (5,2,9) 
) t
ORDER BY
 CASE id
  WHEN 5 THEN 1 
  WHEN 2 THEN 2
  WHEN 9 THEN 3
 END