ORDER BY the IN value list

sql postgresql sql-order-by sql-in
nutcracker picture nutcracker · May 15, 2009 · Viewed 114.2k times · Source

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

user80168 picture user80168 · May 15, 2009

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

Related questions

PostgreSQL DISTINCT ON with different ORDER BY

I want to run this query: SELECT DISTINCT ON (address_id) purchases.address_id, purchases.* FROM purchases WHERE purchases.product_id = 1 ORDER BY purchases.purchased_at DESC But I get this error: PG::Error: ERROR: SELECT DISTINCT ON expressions must …

Read More
sql ORDER BY multiple values in specific order?

Ok I have a table with a indexed key and a non indexed field. I need to find all records with a certain value and return the row. I would like to know if I can order by multiple values. …

Read More
Sort by column ASC, but NULL values first?

I need to sort a PostgreSQL table ascending by a date/time field, e.g. last_updated. But that field is allowed to be empty or null and I want records with null in last_updated come before non-null last_…

Read More