Search code examples
sqlpostgresqlsql-order-bysql-in

ORDER BY the IN value list

I have a simple SQL query in PostgreSQL 8.3 that grabs a bunch of comments. I provide a sorted list of values to the IN construct in the WHERE clause:

SELECT * FROM comments WHERE (comments.id IN (1,3,2,4));

This returns comments in an arbitrary order which in my happens to be ids like 1,2,3,4.

I want the resulting rows sorted like the list in the IN construct: (1,3,2,4).
How to achieve that?

Solution

  • You can do it quite easily with (introduced in PostgreSQL 8.2) VALUES (), ().

    Syntax will be like this:

    select c.*
from comments c
join (
  values
    (1,1),
    (3,2),
    (2,3),
    (4,4)
) as x (id, ordering) on c.id = x.id
order by x.ordering