OraFAQ Forum: SQL & PL/SQL » Order using the rownum

Home » SQL & PL/SQL » SQL & PL/SQL » Order using the rownum

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Order using the rownum [message #21299]

Thu, 25 July 2002 06:03

Giridhar
Messages: 38
Registered: May 2001

Member

Hi,
For the following query
select rownum,ename from emp order by ename;
we get the result as follows

ROWNUM ENAME
---------- ----------
11 ADAMS
2 ALLEN
6 BLAKE
7 CLARK
13 FORD
12 JAMES
4 JONES
9 KING
5 MARTIN
14 MILLER
8 SCOTT

ROWNUM ENAME
---------- ----------
1 SMITH
10 TURNER
3 WARD

Problem here is rownum is not ordered.It happens becoz the rownum would be assigned as soon as it is fetched and after that the ename would be sorted.

But my friend told us that there is a way to get the rownum also in ascending order as well as the data in the order.
QUery for that is

select rownum, ename from emp a, dual b
where a.ename = b.dummy(+)
order by a.ename

Result is

ROWNUM ENAME
---------- ----------
1 ADAMS
2 ALLEN
3 BLAKE
4 CLARK
5 FORD
6 JAMES
7 JONES
8 KING
9 MARTIN
10 MILLER
11 SCOTT

ROWNUM ENAME
---------- ----------
12 SMITH
13 TURNER
14 WARD

But we really dont know how it is working .
can anyone explain us how it is working??

Thanks in advance

Giridhar KOdakalla

Report message to a moderator

Re: Order using the rownum [message #21300 is a reply to message #21299]

Thu, 25 July 2002 06:45

Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

rownum allocates a number based on the output of the query only(resultset).
so in first query you select the rownum ,columns and then order them by ename.
in second , u select the columns, order them, and then apply the rownum

  1* select rownum,ename from emp order by ename
SQL> /

    ROWNUM ENAME
---------- ----------
        11 ADAMS
         2 ALLEN
         6 BLAKE
         7 CLARK
        13 FORD
        12 JAMES
         4 JONES
         9 KING
         5 MARTIN
        14 MILLER
         8 SCOTT
         1 SMITH
        10 TURNER
         3 WARD

14 rows selected.
SQL> ed
Wrote file afiedt.buf

  1  select rownum,ename from
  2* (select ename from emp order by ename)
SQL> /

    ROWNUM ENAME
---------- ----------
         1 ADAMS
         2 ALLEN
         3 BLAKE
         4 CLARK
         5 FORD
         6 JAMES
         7 JONES
         8 KING
         9 MARTIN
        10 MILLER
        11 SCOTT
        12 SMITH
        13 TURNER
        14 WARD

14 rows selected.

Report message to a moderator

Previous Topic:	dblink
Next Topic:	Hierarchial Query

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 19 17:16:18 CDT 2024