ORDER BY the IN value list

Just because it is so difficult to find and it has to be spread: in mySQL this can be done much simpler, but I don't know if it works in other SQL.

SELECT * FROM `comments`
WHERE `comments`.`id` IN ('12','5','3','17')
ORDER BY FIELD(`comments`.`id`,'12','5','3','17')

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

With Postgres 9.4 this can be done a bit shorter:

select c.*
from comments c
join (
  select *
  from unnest(array[43,47,42]) with ordinality
) as x (id, ordering) on c.id = x.id
order by x.ordering;

Or a bit more compact without a derived table:

select c.*
from comments c
  join unnest(array[43,47,42]) with ordinality as x (id, ordering) 
    on c.id = x.id
order by x.ordering

Removing the need to manually assign/maintain a position to each value.

With Postgres 9.6 this can be done using array_position():

with x (id_list) as (
  values (array[42,48,43])
)
select c.*
from comments c, x
where id = any (x.id_list)
order by array_position(x.id_list, c.id);

The CTE is used so that the list of values only needs to be specified once. If that is not important this can also be written as:

select c.*
from comments c
where id in (42,48,43)
order by array_position(array[42,48,43], c.id);

In Postgres 9.4 or later, this is simplest and fastest:

SELECT c.*
FROM   comments c
JOIN   unnest('{1,3,2,4}'::int[]) WITH ORDINALITY t(id, ord) USING (id)
ORDER  BY t.ord;

WITH ORDINALITY was introduced with in Postgres 9.4.
No need for a subquery, we can use the set-returning function like a table directly. (A.k.a. "table-function".)
A string literal to hand in the array instead of an ARRAY constructor may be easier to implement with some clients.
For convenience (optionally), copy the column name we are joining to (id in the example), so we can join with a short USING clause to only get a single instance of the join column in the result.
Works with any input type. If your key column is of type text, provide something like '{foo,bar,baz}'::text[].

Detailed explanation:

Related