DB2 - SQL Select Fetch Clause

How to Limit Query Results for DB2 Databases?

There are two ways to limit the result.

1. LIMIT / OFFSET
2. FETCH FIRST

Why do we need to Limit the Query Results?

Suppose that you write an application that requires information on only the 10 student with the highest total marks. To return only the rows of the Students table for those 20 student, This can be achived by Fetch First or Limit clause.

Also, This clause is useful on large tables with thousands of records. Returning a large number of records can impact performance.

DB2 Fetch Clause:

The Fetch clause allows you to limit the number of rows returned by the query.

Syntax:

SELECT select_list FROM table_name
  OFFSET n ROWS
  FETCH {FIRST | NEXT } m {ROW | ROWS} ONLY

Where,

'n' is the number of rows to skip.

'm' is the number of rows to return. The FIRST and NEXT, ROW and ROWS are interchangeable respectively. They are used for the semantic purpose.

DB2 Database:

Below is a selection from the "Product" table in the DB2 database.

Example 1:

The followng SQl query uses the FETCH clause to get the top 4 product in the "Products" table.

SELECT *
FROM Product
  FETCH FIRST 4 ROWS ONLY;

Result:

Example 2:

To skip the first 2 products and return the next 3 products, We use the FETCH OFFSET clause as follows:

SELECT *
  FROM Product
  OFFSET 2 ROWS
  FETCH NEXT 3 ROWS ONLY;

Result:

If you have any doubts or queries related to this chapter, get them clarified from our Mainframe experts on ibmmainframer Community!