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

From: Daniel Fink <Daniel.Fink_at_Sun.COM>
Date: Fri, 16 Apr 2004 12:22:47 -0600
Message-id: <40802477.F8532A49@sun.com>

If you are able to dynamically generate the statement, you can use the inlist as an input for the order by. The INSTR function will return the starting position for each string, so the first string will have a lower value than the second, etc.

select empno, ename
from emp
where empno in (7902,7788,7369,7900)
order by instr('7902,7788,7369,7900',empno);

EMPNO ENAME
---------- ----------

      7902 FORD
      7788 SCOTT
      7369 SMITH
      7900 JAMES

Create a pl/sql wrapper that accepts the INLIST and also use it in the order by.

Interestingly enough, my first cut at this revealed that the values were being returned in reverse order! It had to to with the execution plan, so you can't count on it being the same every time.

  1 select empno, ename
  2 from emp
  3* where empno in (7902,7788,7369,7900) SQL> /      EMPNO ENAME
---------- ----------

      7900 JAMES
      7369 SMITH
      7788 SCOTT
      7902 FORD

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE 1 0 CONCATENATION

   2    1     TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
   3    2       INDEX (UNIQUE SCAN) OF 'PK_EMP' (UNIQUE)
   4    1     TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
   5    4       INDEX (UNIQUE SCAN) OF 'PK_EMP' (UNIQUE)
   6    1     TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
   7    6       INDEX (UNIQUE SCAN) OF 'PK_EMP' (UNIQUE)
   8    1     TABLE ACCESS (BY INDEX ROWID) OF 'EMP'
   9    8       INDEX (UNIQUE SCAN) OF 'PK_EMP' (UNIQUE)

Daniel Fink

"Powell, Mark D" wrote:
>
> 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 --

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:21:26 CDT