ORDER BY the IN value list

Question

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?

Answer 1

In Postgres 9.4 or later, this is probably 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;

• Using the new WITH ORDINALITY, that @a_horse already mentioned.

• We don't need a subquery, we can use the set-returning function like a table.

• A string literal to hand in the array instead of an ARRAY constructor may be easier to implement with some clients.

Detailed explanation:

• PostgreSQL unnest() with element number

Answer 2

I agree with all other posters that say "don't do that" or "SQL isn't good at that". If you want to sort by some facet of comments then add another integer column to one of your tables to hold your sort criteria and sort by that value. eg "ORDER BY comments.sort DESC " If you want to sort these in a different order every time then... SQL won't be for you in this case.

Answer 3

select * from comments where comments.id in 
(select unnest(ids) from bbs where id=19795) 
order by array_position((select ids from bbs where id=19795),comments.id)

here, [bbs] is the main table that has a field called ids, and, ids is the array that store the comments.id .

passed in postgresql 9.6

Answer 4

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

Answer 5

In Postgresql:

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

Answer 6

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