Thread: Ordering returned rows according to a list

Hi all,

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.

Is this possible?

Sebastian

>
> Hi all,
>
> 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.
>
> Is this possible?
>

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

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