Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Record order in SELECT .. IN ( , , , ) queries

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

From: termoPilucco <termop3141927_at_hotmail.com>
Date: Mon, 26 Jan 2004 16:02:37 +0100
Message-ID: <bv3a6f$nkf5o$1@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 Mon Jan 26 2004 - 09:02:37 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US