RE: displaying result sets in the order listed in the IN list
- From: "Khedr, Waleed" <Waleed.Khedr@xxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 16 Apr 2004 14:23:00 -0400
This should work most of the time if the column is indexed:
create table test_ordered_in ( c1 number primary key, c2 number);
insert into test_ordered_in values (1,2);=20
insert into test_ordered_in values (2,5);
insert into test_ordered_in values (3,0);
insert into test_ordered_in values (4,9);
insert into test_ordered_in values (5,7);
select =20
*
from test_ordered_in
where c1 in (1,3,4)
C1 C2
4 9
3 0
1 2
select --+ index(test_ordered_in, ) =20
*
from test_ordered_in
where c1 in (1,3,4)
C1 C2
1 2
3 0
4 9
They idea is to do inlist iterator using the index.
Regards,
Waleed
-----Original Message-----
From: Ivan Chow [mailto:ichow2@xxxxxxxxxxx]
Sent: Friday, April 16, 2004 1:35 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: displaying result sets in the order listed in the IN list
Hello,
I have a table that has the following values:
select * from mytab;
mycol
-----
1
2
3
4
5
6
select * from mytab where mycol in (3,1,4);
mycol
-----
1
3
4
Is it possible to display the values of mycol in the order the literal=20
values are listed in the IN list? The IN list string 3,1,4 is passed in =
by=20
another program and it can be listed in any order. I need the results to =
be=20
display in whatever the order is in the IN list.
My expected result is
mycol
-----
3
1
4
Is that possible? If not, any suggestions?
thanks in advance.
Ivan C.
_________________________________________________________________
Stop worrying about overloading your inbox - get MSN Hotmail Extra =
Storage!=20
http://join.msn.com/?pgmarket=3Den-us&page=3Dhotmail/es2&ST=3D1/go/onm002=
00362ave/direct/01/
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
----------------------------------------------------------------
Please see the official ORACLE-L FAQ: http://www.orafaq.com
----------------------------------------------------------------
To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx
put 'unsubscribe' in the subject line.
--
Archives are at //www.freelists.org/archives/oracle-l/
FAQ is at //www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------
Other related posts:
