How to Limit Query Results for DB2 Databases
Many times users are only interested in a subset of the results returned from an SQL query. All versions of DB2 provide a mechanism for limiting the records returned from an SQL query with the FETCH FIRST clause. Newer versions of DB2 also support the LIMIT and LIMIT OFFSET clauses. Using the FETCH FIRST syntax or LIMIT syntax (for newer DB2 versions) improves the performance of queries when not all results are required. Listed below are some examples explaining how to use FETCH FIRST and LIMIT / OFFSET.
Example 1: Returning the first 100 rows from a table called employee using the FETCH FIRST syntax:
SELECT
*
FROM
employee
FETCH FIRST 100 ROWS ONLY
Example 2: Using the FETCH FIRST syntax with an order by clause
SELECT
*
FROM
employee
ORDER BY
salary DESC
FETCH FIRST 10 ROWS ONLY
Example 3: Using the LIMIT syntax to fetch the first 10 rows from a table named account:
SELECT
*
FROM
account
LIMIT 10
Example 4: Using the LIMIT OFFSET syntax to fetch the first 10 rows from a table named account
starting at row 5:
SELECT
*
FROM
account
LIMIT 10 OFFSET 5
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
- Derby Limit Rows
- Firebird Limit Rows
- H2 Limit Rows
- HSQLDB Limit Rows
- Informix Limit Rows
- Microsoft SQL Server Limit Rows
- MySQL Limit Rows
- Oracle 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
