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.