DB2 Version 7 provides an easy way to limit the results of a SELECT statement using a new clause – the FETCH FIRST n ROWS clause. When the FETCH FIRST n ROWS clause is specified, DB2 will limit the number of rows that are fetched and returned by a SELECT statement. This Version 7 approach requires SQL only and is quite simple and efficient. The FETCH FIRST n ROWS ONLY clause is appended right to the end of the SELECT statement. It is used as follows:
SELECT EMPNO, LASTNAME, FIRSTNME, SALARY
FROM DSN8710.EMP
ORDER BY SALARY DESC
FETCH FIRST 10 ROWS ONLY;
Of course, the value can be any number – not just 10.
For example, to retrieve only the top 4 salaries you would code:
SELECT EMPNO, LASTNAME, FIRSTNME, SALARY
FROM DSN8710.EMP
ORDER BY SALARY DESC
FETCH FIRST 4 ROWS ONLY;
This is the simplest and probably the most elegant solution for limiting the number of rows returned by a DB2 query. This clause is different from the OPTIMIZE FOR n ROWS clause that has been available for several releases of DB2 now. The FETCH FIRST n ROWS ONLY clause will limit the number of rows returned to the specified number, n. Remember that the OPTIMIZE FOR n ROWS clause does not impact the number of rows returned, but is used only by the optimizer for optimizing SQL.
