SELECT E1.EMPNO,E2.EMPNO FROM (SELECT ROWNUM r1,empno FROM emp ORDER BY empno DESC)e1,(SELECT ROWNUM r2,empno FROM emp ORDER BY empno ASC) e2 WHERE e1.r1 = e2.r2
Generating same column of a table with ascending order and descending order Simple query
July 26th, 2008 by Dushyant Nayak · 3 Comments
Tags: SQL and PL/SQL · SQL*Plus
3 responses so far ↓
1 Laurent Schneider // Nov 10, 2008 at 8:26 pm
> SELECT ROWNUM r1,empno FROM emp ORDER BY empno DESC
do not forget rownum is executed after rownum!
2 Laurent Schneider // Nov 10, 2008 at 9:35 pm
Maybe you want to select the table only once to minimize IOs
select
ename,
case
when r1<r2 then lead(ename,abs(r2-r1)) over (order by r1)
when r2<r1 then lag(ename,abs(r1-r2)) over (order by r1)
else ename
end ename
from (
select
ename,
row_number() over (order by ename) r1,
row_number() over (order by ename desc) r2
from LSC_EMP
);
note the query you posted with the rownum is rewrittable as
select
empno,
case
when rownum7 then lag(empno,abs(2*rownum-15)) over (order by 1)
else empno
end empno
from LSC_EMP;
3 Laurent Schneider // Nov 10, 2008 at 9:36 pm
of course, < and > sucks, same on my site
Leave a Comment