Thursday, June 14, 2012

ROWNUM


Tested on an Oracle 9 database. ROWNUM is a pseudo column which you can include in the output  from a SQL query:

SQL> select rownum, object_id from dba_objects
  2  where rownum < 6
  3  /

    ROWNUM  OBJECT_ID
---------- ----------
         1         47
         2         19
         3         17
         4         15
         5         45

SQL>

The first rownum is always 1, the second is always 2 and so on. So if you do not have a rownum of 1 at the start, you will not get any output at all. The following query selects the same rows as the one above:

SQL> select rownum, object_id from dba_objects
  2  where rownum between 1 and 5
  3  /

    ROWNUM  OBJECT_ID
---------- ----------
         1         47
         2         19
         3         17
         4         15
         5         45

SQL>

But the next one does not select rows 2 through 5. It produces no output at all as the first row of output cannot have a rownum of 1:

SQL> select rownum, object_id from dba_objects
  2  where rownum between 2 and 5
  3  /

no rows selected

SQL>

No comments: