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

Thanks, termo:

This is a useful idea. I'm not sure we will implement it, but it's correct.

I use stored functions to return the selected data, passing a 'TABLE OF NUMBER' (=number_tt) array (=id_tt) and using a WHERE USR_ID IN (SELECT COLUMN_VALUE FROM TABLE(CAST (:id_tt AS number_tt))).

So I needed an automated process to return the results in the same order as the ID's are in the array.

Kurta

"termoPilucco" <termop3141927_at_hotmail.com> wrote in message news:<bv3a6f$nkf5o$1_at_ID-188026.news.uni-berlin.de>...
> "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 Tue Jan 27 2004 - 02:52:57 CST