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

Home -> Community -> Mailing Lists -> Oracle-L -> RE: displaying result sets in the order listed in the IN list

RE: displaying result sets in the order listed in the IN list

From: Lex de Haan <lex.de.haan_at_naturaljoin.nl>
Date: Fri, 16 Apr 2004 20:13:14 +0200
Message-ID: <JFEEIGBIDOCCDALDIPLNIEJLCCAA.lex.de.haan@naturaljoin.nl> 





... or better, with a simple CASE expression:
order by case A when 4 then 1

  4             when 1 then 2
  5             when 6 then 3
  6      end





Kind regards,


Lex.





visit my website at http://www.naturaljoin.nl







-----Original Message-----



From: oracle-l-bounce_at_freelists.org


[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Powell, Mark D
Sent: Friday, April 16, 2004 19:06


To: 'oracle-l_at_freelists.org'


Subject: RE: displaying result sets in the order listed in the IN list




I agree with Lex that Oracle does not provide and as far as I know neither
does any other database vendor a feature to supporting sorting on an in-list
but as Steve showed you can do it when the values in the list are static and
know in advance.  I think though that the time has come to replace decode
with the ANSI standard CASE statement:



UT1 > select * from marktest2


  2  where A in (4,1,6);



         A



         1
         4
         6





UT1 > select * from marktest2


  2  where A in (4,1,6)

  3  order by case when A = 4 then 1
  4                when A = 1 then 2
  5                when A = 6 then 3
  6           end




  7  /



         A



         4
         1
         6






HTH -- Mark D Powell --



-----Original Message-----



From: oracle-l-bounce_at_freelists.org


[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Orr, Steve
Sent: Friday, April 16, 2004 1:58 PM


To: oracle-l_at_freelists.org


Subject: RE: displaying result sets in the order listed in the IN list




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_at_freelists.org


[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Lex de Haan
Sent: Friday, April 16, 2004 11:41 AM


To: oracle-l_at_freelists.org


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_at_freelists.org


[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Ivan Chow
Sent: Friday, April 16, 2004 18:35


To: oracle-l_at_freelists.org


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_at_freelists.org put
'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://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_at_freelists.org put
'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://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_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://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_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://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_at_freelists.org
put 'unsubscribe' in the subject line.
--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html


-----------------------------------------------------------------



Received on Fri Apr 16 2004 - 13:09:59 CDT












Original text of this message













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