Select Records In Order Based on an Ordered ID List

Hello!

Let's say I have an ordered list of ids that correspond to an ordered
list of records I want to retrieve, ie. 32,80,10,90,28,5

What's the most efficient way to retrieve the records corresponding to
the ids in the same order as the ids? It seems the IN clause doesn't
preserve ordering, and using UNION to submit the same query with
different id WHERE clauses seems messy/inefficient.

Thanks!
Mike

>Let's say I have an ordered list of ids that correspond to an ordered
>list of records I want to retrieve, ie. 32,80,10,90,28,5
>
>What's the most efficient way to retrieve the records corresponding to
>the ids in the same order as the ids? It seems the IN clause doesn't
>preserve ordering,

There's no ordering in an IN clause to preserve. If you don't ORDER BY,
you have no complaint about the order you get.

>and using UNION to submit the same query with
>different id WHERE clauses seems messy/inefficient.

Create a table ordering with columns consisting of the ids and the
relative ordering.
id ordering
32 1
80 2
10 3
90 4
28 5
5 6

Join the table against the table with the
records. ORDER BY ordering.ordering desc .

This may not be most efficient, but it shouldn't be too bad.

Mike wrote:
> Hello!
>
> Let's say I have an ordered list of ids that correspond to an ordered
> list of records I want to retrieve, ie. 32,80,10,90,28,5
>
> What's the most efficient way to retrieve the records corresponding to
> the ids in the same order as the ids? It seems the IN clause doesn't
> preserve ordering, and using UNION to submit the same query with
> different id WHERE clauses seems messy/inefficient.
>
> Thanks!
> Mike
>

SELECT myval FROM mytable
WHERE id IN (32,80,10,90,28,5)
ORDER BY FIND_IN_SET(id, '32,80,10,90,28,5')

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================