Re: Rownum and sort

From: Thomas J. Kyte <tkyte_at_us.oracle.com>
Date: Wed, 08 Mar 2000 15:46:19 GMT
Message-ID: <8a5sk9$mqo$1@nnrp1.deja.com>

In article <8a5q5i$ksr$1_at_nnrp1.deja.com>, robertchung0909_at_my-deja.com wrote:
>
>
> As everyone knows, "ROWNUM" column in Oracle displays row number in
> order like this:
>
> SQL> select rownum, empno, ename, job from emp;
>
> ROWNUM EMPNO ENAME JOB
> ---------- ---------- ---------- ---------
> 1 7369 SMITH CLERK
snip
> 14 7934 MILLER CLERK
>
> 14 rows selected.
>
> My problem is the fact that rownum is rearranged when I do "order by",
> like this:
>
> SQL> select rownum, empno, ename, job from emp order by ename;
>
> ROWNUM EMPNO ENAME JOB
> ---------- ---------- ---------- ---------
> 11 7876 ADAMS CLERK
snip
> 3 7521 WARD SALESMAN
>
> 14 rows selected.
>

this works only with 8.1 and up. you can order by in a subquery:

1 select rownum, a.*
2* from ( select ename, mgr from emp order by ename ) a ops$tkyte_at_8i> /

ROWNUM ENAME MGR
---------- ---------- ----------

         1 ADAMS            7788
         2 ALLEN            7698
         3 BLAKE            7839
         4 CLARK            7839
         5 FORD             7566
         6 JAMES            7698
         7 JONES            7839
         8 KING
         9 MARTIN           7698
        10 MILLER           7782
        11 SCOTT            7566
        12 SMITH            7902
        13 TURNER           7698
        14 WARD             7698

14 rows selected.

> SQL>
>
> Is there any way to get around this, so that rownum is displayed in
> order even when I do sort? Thank you in advance.
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Thomas Kyte                              tkyte_at_us.oracle.com

Oracle Service Industries
http://osi.oracle.com/~tkyte/index.html --
Opinions are mine and do not necessarily reflect those of Oracle Corp

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Wed Mar 08 2000 - 09:46:19 CST