Re: Record order in SELECT .. IN ( , , , ) queries

"Kurta" <submit_at_galleus.com> wrote:

> When I start a SELECT IN query like this:
>
> SELECT USR_ID, USR_NAME FROM T_USR WHERE USR_ID IN (4, 2, 5, 1, 3);
If you want be sure you order be 4,2,5,1,3 (or reverse) you have to create a temp table with a couple of fields (a number with autoincrement through a sequence, and your USR_ID value) then you have to join your query by USR_ID with new table and order by the autoincrement

But, if your query affects few records, you can do something like this: select USR_ID, USR_NAME from T_USR where USR_ID in (4, 2, 5, 1, 3) order by

(CASE WHEN USR_ID=4 THEN 1 ELSE 0 END)+
(CASE WHEN USR_ID=2 THEN 2 ELSE 0 END)+
(CASE WHEN USR_ID=5 THEN 3 ELSE 0 END)+
(CASE WHEN USR_ID=1 THEN 4 ELSE 0 END)+
(CASE WHEN USR_ID=3 THEN 5 ELSE 0 END)

termoPilucco Received on Mon Jan 26 2004 - 09:02:37 CST