|René Nyffenegger's collection of things on the web|
René Nyffenegger on Oracle - Most wanted - Feedback
- Follow @renenyffenegger
ROWNUM in SQL
rownum is a pseudo column. It numbers the records in a result set. The first record that meets the where criteria in a select statement is given rownum=1, and every subsequent record meeting that same criteria increases rownum.
After issuing a select statement, one of the last steps that oracle does is to assign an increasing (starting with 1, increased by 1) number to each row returned. The value of this row number can always be queried with rownum in a select statement:
select rownum, column_1, column_2 from table_1, table_2 where field_3 = 'some value'
It is important to realize that the first row's rownum is always 1. This implies that the following query won't return a single row:
select column_1, column_2 from table_1, table_2 where field_3 = 'some value' and rownum > 5
This is so because the first row would have to meet the following two mutually excluding criterias:
In order to do this query in the (probably) intended spirit, a sub-query must be executed:
select column_1, column_2 from ( select rownum r_, column_1, column_2 from table_1, table_2 where field_3 = 'some value' ) where r_ > 5
See also On a table's first five rows.