Path: utzoo!utgpu!news-server.csri.toronto.edu!rutgers!apple!usc!zaphod.mps.ohio-state.edu!wuarchive!emory!hubcap!ncrcae!ncrlnk!usglnk!usperb!daved
From: daved@usperb.Dayton.NCR.COM (Dave Dresselhouse)
Newsgroups: comp.databases
Subject: ORACLE ROWNUM Pseudo-column
Message-ID: <561@usperb.Dayton.NCR.COM>
Date: 15 Nov 90 15:46:43 GMT
Reply-To: daved@usperb.Dayton.NCR.COM (Dave Dresselhouse)
Distribution: usa
Organization: NCR Corporation, U.S. Group
Lines: 53

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.

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?

----------------------------------------------------------------------------
Dave Dresselhouse                      Dave.Dresselhouse@Dayton.NCR.COM
NCR Corporation
U. S. Group
----------------------------------------------------------------------------