Re: displaying result sets in the order listed in the IN list
- From: Daniel Fink <Daniel.Fink@xxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Fri, 16 Apr 2004 12:22:47 -0600
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@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:
