Fetching a limited number of rows

You can specify the fetch clause in a SELECT statement to limit the number of rows in the result table of a query.

About this task

Begin program-specific programming interface information.

In some applications, you execute queries that can return a large number of rows, but you need only a small subset of those rows. Retrieving the entire result table from the query can be inefficient.

Procedure

To limit the number of rows in the result table of a query:

Specify the FETCH FIRST n ROWS ONLY clause in the SELECT statement.

Results

DB2® limits the number of rows in the result table of a query to n rows.

For distributed queries that use DRDA access, FETCH FIRST n ROWS ONLY, DB2 prefetches only n rows.

Example

Suppose that you write an application that requires information on only the 20 employees with the highest salaries. To return only the rows of the employee table for those 20 employees, you can write a query as shown in the following example:

SELECT LASTNAME, FIRSTNAME, EMPNO, SALARY
  FROM EMP
  ORDER BY SALARY DESC
  FETCH FIRST 20 ROWS ONLY;

You can also use FETCH FIRST n ROWS ONLY within a subquery.

SELECT * FROM EMP 
WHERE EMPNO IN (
	SELECT RESPEMP FROM PROJECT 
	ORDER BY PROJNO
       FETCH FIRST 3 ROWS ONLY)

End program-specific programming interface information.