Quantcast
OracleBrains.Com header image 2

Generating same column of a table with ascending order and descending order Simple query

July 26th, 2008 by Dushyant Nayak · 3 Comments

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

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