27 November 2006

ROWNUM and Aggregate Functions

When working on aggregate functions, you must be aware of rownum. Resultset firstly taken with rownum and then aggregate functions are processed.
Look at example below:

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as SYS

SQL>
SQL> drop table testtab;

Table dropped
SQL> CREATE TABLE testtab AS  SELECT o.object_name n, mod(rownum, 5) i, rownum r FROM all_objects o WHERE rownum < 11;
Table created
SQL> SELECT * FROM testtab;
N                                       I          R
------------------------------ ---------- ----------
ICOL$                                   1          1
I_USER1                                 2          2
CON$                                    3          3
UNDO$                                   4          4
C_COBJ#                                 0          5
I_OBJ#                                  1          6
PROXY_ROLE_DATA$                        2          7
I_IND1                                  3          8
I_CDEF2                                 4          9
I_PROXY_ROLE_DATA$_1                    0         10

10 rows selected
SQL> SELECT * FROM testtab WHERE i = 0;
N                                       I          R
------------------------------ ---------- ----------
C_COBJ#                                 0          5
I_PROXY_ROLE_DATA$_1                    0         10

SQL> SELECT max(r) FROM testtab WHERE i = 0 and rownum = 1;
    MAX(R)
----------
         5

SQL> SELECT max(r) FROM testtab WHERE i = 0;
    MAX(R)
----------
        10

SQL> SELECT * FROM (SELECT max(r) FROM testtab WHERE i = 0)  WHERE rownum = 1;
    MAX(R)
----------
        10

SQL>

No comments: