OraFAQ Forum: SQL & PL/SQL » auto numbering records in a query

Home » SQL & PL/SQL » SQL & PL/SQL » auto numbering records in a query

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

auto numbering records in a query [message #7897]

Wed, 16 July 2003 00:38

Alex
Messages: 190
Registered: August 1999

Senior Member

Hello Fans!
Is it possible to make a query with a column which numbers rows in ascending order. If so, can anybody place an example.

Report message to a moderator

Re: auto numbering records in a query [message #7898 is a reply to message #7897]

Wed, 16 July 2003 02:18

Maaher
Messages: 7065
Registered: December 2001

Senior Member

ROWNUM:

  1  select rownum
  2       , deptno
  3       , dname
  4    from dept
  5*  order by deptno asc
SQL> /

    ROWNUM     DEPTNO DNAME
---------- ---------- --------------
         1         10 ACCOUNTING
         2         20 RESEARCH
         3         30 SALES
         4         40 OPERATIONS

4 rows selected.

SQL> ed
Wrote file afiedt.buf

  1  select rownum
  2       , deptno
  3       , dname
  4    from dept
  5*  order by deptno desc
SQL> /

    ROWNUM     DEPTNO DNAME
---------- ---------- --------------
         1         40 OPERATIONS
         2         30 SALES
         3         20 RESEARCH
         4         10 ACCOUNTING

4 rows selected.

I suggest you read the doc concerning ROWNUM, because its usage is limited (it is a PSEUDO-COLUMN)

MHE

Report message to a moderator

Re: auto numbering records in a query [message #7911 is a reply to message #7898]

Wed, 16 July 2003 19:13

Barbara Boehmer
Messages: 9090
Registered: November 2002
Location: California, USA

Senior Member

It is important that the ordering be done within an inner sub-query and then numbering using rownum be done within an outer query. Otherwise, the results may not be correct. If the numbering using rownum and the ordering are done at the same level, Oracle does the numbering using rownum first, then orders the records. Please see the examples below:

SQL> -- just happens to produce the correct result this time (but not reliable method):
SQL> SELECT   ROWNUM, deptno, dname
  2  FROM     dept
  3  ORDER BY deptno
  4  /

    ROWNUM     DEPTNO DNAME                                                     
---------- ---------- --------------                                            
         1         10 ACCOUNTING                                                
         2         20 RESEARCH                                                  
         3         30 SALES                                                     
         4         40 OPERATIONS                                                

SQL> INSERT INTO dept (deptno, dname) VALUES (60, 'TEST60')
  2  /

1 row created.

SQL> INSERT INTO dept (deptno, dname) VALUES (50, 'TEST50')
  2  /

1 row created.

SQL> -- same query mow does not produce correct result because rows are:
SQL> --   numbered as they are retrieved in unpredictable order
SQL> --     (could be based on primary key or full table scan or other, depending on conditions)
SQL> --   then rows are ordered after numbering
SQL> SELECT   ROWNUM, deptno, dname
  2  FROM     dept
  3  ORDER BY deptno
  4  /

    ROWNUM     DEPTNO DNAME                                                     
---------- ---------- --------------                                            
         1         10 ACCOUNTING                                                
         2         20 RESEARCH                                                  
         3         30 SALES                                                     
         4         40 OPERATIONS                                                
         6         50 TEST50                                                    
         5         60 TEST60                                                    

6 rows selected.

SQL> -- produces the correct result because rows are:
SQL> --   ordered within sub-query
SQL> --   then retrieved and numbered from the pre-ordered sub-query
SQL> SELECT   ROWNUM, deptno, dname
  2  FROM     (SELECT	deptno, dname
  3  	       FROM	dept
  4  	       ORDER BY deptno)
  5  /

    ROWNUM     DEPTNO DNAME                                                     
---------- ---------- --------------                                            
         1         10 ACCOUNTING                                                
         2         20 RESEARCH                                                  
         3         30 SALES                                                     
         4         40 OPERATIONS                                                
         5         50 TEST50                                                    
         6         60 TEST60                                                    

6 rows selected.

Report message to a moderator

Re: auto numbering records in a query [message #7919 is a reply to message #7911]

Wed, 16 July 2003 23:52

Maaher
Messages: 7065
Registered: December 2001

Senior Member

True,
I already thought I was missing stomething.

MHE

Report message to a moderator

Previous Topic:	Returning resultsets
Next Topic:	To see the Trigger statement....

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Apr 26 22:32:12 CDT 2024