Re: Ordering returned rows according to a list - Mailing list pgsql-general

Quoth "Adam Rich" <adam.r@sbcglobal.net>:
>> I'd like to make a single query that returns a number of rows using a
>> 'WHERE id IN (<list-of-ids>)' condition, but I'd like the rows to be
>> returned in the order in which the ids are given in the list.
>>
> Depending on how many IDs you have in your list, you can accomplish this
> with a CASE statement:
>
> SELECT *
> FROM MYTABLE
> WHERE id IN (6, 9, 3)
> ORDER BY CASE id
> WHEN 6 then 1
> WHEN 9 then 2
> WHEN 3 then 3 END

Thanks for this suggestion Adam.

You say "depending on how many IDs there are in the list".  The query is
constructed programatically so unless there's a limit on the number of
conditions a CASE clause can handle, this is the way I'll go about it.

Sebastian