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.
Many other databases also support limiting rows returned from queries. Listed below are links that show how to limit rows for other popular databases:
- Cassandra Limit Rows
- DB2 Limit Rows
- Derby Limit Rows
- Firebird Limit Rows
- H2 Limit Rows
- HSQLDB Limit Rows
- Informix Limit Rows
- Microsoft SQL Server Limit Rows
- MySQL Limit Rows
- Pervasive Limit Rows
- PostgreSQL Limit Rows
- Redshift Limit Rows
- Salesforce Limit Rows
- SimpleDB Limit Rows
- SQLite Limit Rows
- Sybase Limit Rows
- Teradata Limit Rows
