daved@usperb.Dayton.NCR.COM (Dave Dresselhouse) (11/15/90)
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
----------------------------------------------------------------------------lugnut@sequent.UUCP (Don Bolton) (11/20/90)
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 :-)