From oracle-l-bounce@freelists.org Fri Apr 16 13:03:49 2004 Return-Path: Received: from air189.startdedicated.com (root@localhost) by orafaq.com (8.11.6/8.11.6) with ESMTP id i3GI3mg29729 for ; Fri, 16 Apr 2004 13:03:48 -0500 X-ClientAddr: 206.53.239.180 Received: from turing.freelists.org (freelists-180.iquest.net [206.53.239.180]) by air189.startdedicated.com (8.11.6/8.11.6) with ESMTP id i3GI3j629724 for ; Fri, 16 Apr 2004 13:03:46 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 1A90472D283; Fri, 16 Apr 2004 12:57:34 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 16297-100; Fri, 16 Apr 2004 12:57:33 -0500 (EST) Received: from turing (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 2C0BE72CF99; Fri, 16 Apr 2004 12:57:33 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Fri, 16 Apr 2004 12:56:16 -0500 (EST) X-Original-To: oracle-l@freelists.org Delivered-To: oracle-l@freelists.org Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id DA06B72CDAB for ; Fri, 16 Apr 2004 12:56:15 -0500 (EST) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 16297-76 for ; Fri, 16 Apr 2004 12:56:15 -0500 (EST) Received: from ahmler4.mail.eds.com (ahmler4.mail.eds.com [192.85.154.77]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 1176472CD81 for ; Fri, 16 Apr 2004 12:56:15 -0500 (EST) Received: from ahmlir4.mail.eds.com (ahmlir4-2.mail.eds.com [192.85.154.134]) by ahmler4.mail.eds.com (8.12.10/8.12.9) with ESMTP id i3GI72St032030 for ; Fri, 16 Apr 2004 14:07:04 -0400 Received: from ahmlir4.mail.eds.com (localhost [127.0.0.1]) by ahmlir4.mail.eds.com (8.12.10/8.12.10) with ESMTP id i3GI6SI9015503 for ; Fri, 16 Apr 2004 14:06:28 -0400 Received: from USAHM102.amer.corp.eds.com (usahm102.exmi01.exch.eds.com [207.37.138.190]) by ahmlir4.mail.eds.com (8.12.10/8.12.10) with ESMTP id i3GI6RE5015498 for ; Fri, 16 Apr 2004 14:06:28 -0400 Received: by USAHM102.amer.corp.eds.com with Internet Mail Service (5.5.2657.72) id ; Fri, 16 Apr 2004 14:06:16 -0400 Message-ID: <564DE4477544D411AD2C00508BDF0B6A1CE0AAB6@USAHM018.amer.corp.eds.com> From: "Powell, Mark D" To: "'oracle-l@freelists.org'" Subject: RE: displaying result sets in the order listed in the IN list Date: Fri, 16 Apr 2004 14:06:25 -0400 MIME-Version: 1.0 X-Mailer: Internet Mail Service (5.5.2657.72) Content-type: text/plain X-Virus-Scanned: by amavisd-new at freelists.org Content-Transfer-Encoding: 8bit X-archive-position: 3252 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-To: oracle-l-bounce@freelists.org X-original-sender: mark.powell@eds.com Precedence: normal Reply-To: oracle-l@freelists.org X-list: oracle-l X-Virus-Scanned: by amavisd-new at freelists.org 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@freelists.org [mailto:oracle-l-bounce@freelists.org]On Behalf Of Orr, Steve Sent: Friday, April 16, 2004 1:58 PM To: oracle-l@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@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Lex de Haan Sent: Friday, April 16, 2004 11:41 AM To: oracle-l@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 =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@freelists.org [mailto:oracle-l-bounce@freelists.org]On Behalf Of Ivan Chow Sent: Friday, April 16, 2004 18:35 To: oracle-l@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@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@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@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@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 -----------------------------------------------------------------