RE: displaying result sets in the order listed in the IN list
- From: "Orr, Steve" <sorr@xxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 16 Apr 2004 11:57:54 -0600
Well an IN list is usually pretty small/static so it wouldn't be hard to
use a decode on the same values.
connect scott/tiger
SQL> select deptno, dname, loc, decode(deptno,10,1,30,2,20,3) from dept
2 where deptno in (10,30,20)
3* order by 4;=20
DEPTNO DNAME LOC DECODE(DEPTNO,10,1,30,2,20,3)
---------- -------------- ------------- -----------------------------
10 ACCOUNTING NEW YORK 1
30 SALES CHICAGO 2
20 RESEARCH DALLAS 3
Steve Orr
Bozeman, Montana
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Lex de Haan
Sent: Friday, April 16, 2004 11:41 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: RE: displaying result sets in the order listed in the IN list
this is not possible in the relational world.
to me this sounds like the famous "missing attribute" issue;
if this order is important for whatever reason, they forgot to design it
properly. anyway, it is impossible to make the order of elements listed
in an IN-list change the order of your result; you will need some other
attribute (the missing one) for an ORDER BY clause. Kind regards, Lex.
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D
visit my website at: http://www.naturaljoin.nl
<http://www.naturaljoin.nl>
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Ivan Chow
Sent: Friday, April 16, 2004 18:35
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
values are listed in the IN list? The IN list string 3,1,4 is passed in
by another program and it can be listed in any order. I need the results
to be 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!
http://join.msn.com/?pgmarket=3Den-us&page=3Dhotmail/es2&ST=3D1/go/onm002=
00362
ave/
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
-----------------------------------------------------------------
-- Binary/unsupported file stripped by Ecartis --
-- Type: text/x-vcard
-- File: Lex de Haan.vcf
----------------------------------------------------------------
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:
