Limiting Oracle Query Results Using SQL
The Oracle database contains a special pseudo-column named rownum. This column can be used in SQL select queries to limit the results of Oracle queries. This can be especially useful when querying very large tables in cases where the user is only interested in the first so many rows from the table. It is also useful as a performance enhancement for returning ordered records when the user is only interested in a subset of the ordered records.
Listed below are examples of queries using the Oracle rownum syntax.
Example 1: Returning the first 100 rows from a table called employee. Note that
without including ordering clauses such as in the example 2 below, this query would typically
return the rows in the order they were inserted into the table.
select * from employee where rownum <= 100
Example 2: Returning the first 100 rows in order of salary from a table called employee:
select * from
(select * from employee order by salary desc)
where rownum <= 100
Note that the rownum query has to be wrapped around an inner select that actually does the order by.