ORDER BY the IN value list

Question

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

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:

• PostgreSQL unnest() with element number

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')

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);

I think this way is better :

SELECT * FROM "comments" WHERE ("comments"."id" IN (1,3,2,4))
    ORDER BY  id=1 DESC, id=3 DESC, id=2 DESC, id=4 DESC

Another way to do it in Postgres would be to use the idx function.

SELECT *
FROM comments
ORDER BY idx(array[1,3,2,4], comments.id)

Don't forget to create the idx function first, as described here: http://wiki.postgresql.org/wiki/Array_Index

In Postgresql:

select *
from comments
where id in (1,3,2,4)
order by position(id::text in '1,3,2,4')