DB2 - SQL Select Limit 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 LIMIT Clause:

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

Syntax:

SELECT select_list FROM table_name
    LIMIT n [OFFSET m];

Where,

'm' is the number of rows to skip before returning the n rows.

'n' is the number of rows to be returned.

You can also write the LIMIT syntax like below,

LIMIT m, n;

This syntax represents skipping m rows and returning the next n rows from the result set.

DB2 Database:

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

Example 1:

Using the LIMIT syntax to fetch the first 4 rows from a "Product" table.

SELECT *
  FROM Product
      LIMIT 4;

Resut:

Example 2:

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

SELECT *
  FROM Product
      LIMIT 3 OFFSET 2;

Resut:

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