ROWNUM Pseudocolumn in Oracle

Oracle selects a row from a table and ROWNUM returns a number indicating the order in which Oracle selects the row from a table. Thus the first row returned by a query has ROWNUM value 1, second row returned by the query has a ROWNUM value 2 and so on.

Use of ROWNUM is particularly useful whenever we want to see some rows from a big table. For example in a table there is 10000 row and we want to see the sample of 10 rows. In this case we can use rownum as ,

SELECT * FROM T WHERE ROWNUM<=10;

One thing we need to remember that ROWNUM does not ensure any order of the row. To return a row in an order you must have to use order by keyword. The following example will make you clear.

SQL> create table rownum_test( a number);
Table created.

SQL> insert into rownum_test values(1);
1 row created.

SQL> insert into rownum_test values(2);
1 row created.

SQL> insert into rownum_test values(3);
1 row created.

SQL> select a, rownum from rownum_test ;
A ROWNUM
---------- ----------
1 1
2 2
3 3


SQL> delete from rownum_test where A=2;
1 row deleted.

SQL> insert into rownum_test values(4);
1 row created.

SQL> select a, rownum from rownum_test ;
A ROWNUM
---------- ----------
1 1
3 2
4 3

SQL> insert into rownum_test values(2);
1 row created.

SQL> select a, rownum from rownum_test ;
A ROWNUM
---------- ----------
1 1
3 2
4 3
2 4

To use rownum and return top 4 roes in a ascending order use it as,
SQL> select * from (select * from rownum_test order by a) where rownum<=4;

A
----------
1
2
3
4

Always remember conditions testing for ROWNUM values greater than a positive integer are always false. For example, this query returns no rows,

SQL> select * from rownum_test where rownum>1;
no rows selected

This is because the first row fetched is assigned a ROWNUM of 1 and makes the condition false as 1is not grater than 1. The second row to be fetched is now the first row and is also assigned a ROWNUM of 1 and again makes the condition false. Thus all rows subsequently fail to satisfy the condition, so no rows are returned.

Related Documents:
----------------------------
Pseudocolumns in Oracle