Path: utzoo!utgpu!news-server.csri.toronto.edu!rutgers!mit-eddie!uw-beaver!zephyr.ens.tek.com!tektronix!sequent!lugnut
From: lugnut@sequent.UUCP (Don Bolton)
Newsgroups: comp.databases
Subject: Re: ORACLE ROWNUM Pseudo-column
Message-ID: <46907@sequent.UUCP>
Date: 20 Nov 90 00:54:17 GMT
References: <561@usperb.Dayton.NCR.COM>
Reply-To: lugnut@sequent.UUCP (Don Bolton)
Distribution: usa
Organization: Sequent Computer Systems, Inc
Lines: 63

In article <561@usperb.Dayton.NCR.COM> daved@usperb.Dayton.NCR.COM (Dave Dresselhouse) writes:
>Is the notion of the ROWNUM pseudo-column valid when joining multiple tables
>and performing group functions?
>
>When doing a group function (such as SUM), ORACLE doesn't appear to be
>returning a sequential ROWNUM indicating the sequence of the composite row
>being returned to the application.
>
ROWNUM is assigned BEFORE the rows are ordered

>The ORACLE docs are a little sketchy in the definition of the ROWNUM
>pseudo-column. Am I misinterpreting the purpose of ROWNUM???
>
>Consider the following simple example:
>     List the TOP 100 salespeople according to total earnings.
>     This query is derived from the following tables:
>
>                EMP                        EARNINGS
>                =========                  =========
>                ID  (Employee ID)          ID  (Employee ID)
>                NAME  (Employee Name)      TYPE (ie: Salary, Bonus, Comm.)
>                                           AMOUNT ($$$)
>
>                                           (Type code 1 indicates Salary,
>                                            any other code is Commission.)
>
>     The format of the desired report is:
>                                       TOTAL       TOTAL       TOTAL
>          EMPLOYEE-ID  EMPLOYEE NAME   SALARY   COMMISSION    EARNINGS
>          -----------  -------------   ------   ----------  --------------
>
>     In order to limit the report to the first 100 employees, the following
>     query is issued:
>
>          SELECT EMP.ID, EMP.NAME,
>                 SUM(DECODE(EARNINGS.TYPE,1,EARNINGS.AMOUNT,0)) SALARY,
>                 SUM(DECODE(EARNINGS.TYPE,1,0,EARNINGS.AMOUNT)) COMMISSION,
>                 SUM(EARNINGS.AMOUNT) TOTAL_EARNINGS
>            FROM EARNINGS, EMP
>           WHERE EARNINGS.ID = EMP.ID
>             AND ROWNUM <= 100
>           GROUP BY EMP.ID, EMP.NAME
>           ORDER BY SUM(EARNINGS.AMOUNT) DESCENDING
>
>     The results of this query are unpredictable (wrong). The rows returned
>do not match the first 100 rows returned by an identical query without the
>ROWNUM constraint! This leads me to believe that the ROWNUM returned is
>actually the ROWNUM of some intermediate step in the grouping process, and not
>the sequential number of the row being returned to the application. Is it
>proper to use the ROWNUM pseudo-column in a query of this type?
>

Never tried ROWNUM, but have made extensive use of ROWID which *is*
Oracle's sequential rownumber.

the SQL Language Refrence Manual Version 6.0 Page 3-26

"Returns a number indicating the sequence in which a row was selected
from a table or set of joined rows.

The first row selected has a ROWNUM of 1, the second has 2,etc"

There is more detail too but I'm a lazy typist :-)