PostgreSQL ORDER BY values in IN() clause

Question

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?

Answer 1

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

Answer 2

From documentation:

Tip: Grouping without aggregate expressions effectively calculates the set of distinct values in a column. This can also be achieved using the DISTINCT clause (see Section 7.3.3).

SQL query:

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