DB2 Tutorial

  • Home
  • Start Here
  • Basics
  • Views
  • Triggers
  • Indexes
  • Functions
    • Aggregate Functions
    • Date Functions
    • String Functions
    • Window Functions
Home » Db2 Basics » Db2 FETCH

Db2 FETCH

Summary: in this tutorial, you will learn how to use the Db2 FETCH clause to limit the number of rows returned by a query.

Introduction to Db2 FETCH clause

When you use the SELECT statement to query data from a table, you may get a large number of rows. However, you only need a small subset of these rows. Therefore, to limit the rows returned by a query, you use the FETCH clause as follows:

OFFSET n ROWS FETCH {FIRST | NEXT } m {ROW | ROWS} ONLY

In this syntax:

  • 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.

Notice that the FETCH clause is an ANSI-SQL version of the LIMIT clause.

Similar to the LIMIT clause, you should always use the FETCH clause with the ORDER BY clause to get the returned rows in a specified order.

Db2 FETCH clause examples

We will use the books table from the sample database to demonstrate the FETCH clause.

1) Using Db2 FETCH clause to get the top-N rows

This example uses the FETCH clause to get the top 10 books by ratings:

SELECT title, rating FROM books ORDER BY rating DESC FETCH FIRST 10 ROWS ONLY;

The following picture shows the output:

In this example:

  • The ORDER BY clause sorts books by ratings from high to low.
  • The FETCH clause picks only the first 10 rows, which have the highest ratings.

2) Using Db2 OFFSET FETCH for pagination example

Suppose, you want to display books in pages sorted by ratings, each page has 10 books.

The following query uses the OFFSET FETCH clause to get the books on the second page:

SELECT title, rating FROM books ORDER BY rating DESC OFFSET 10 ROWS FETCH NEXT 10 ROWS ONLY;

Here is the result set:

In this example:

  • The OFFSET clause skips the first 10 rows which are on the first page.
  • The FETCH clause picks the next 10 rows.

In this tutorial, you have learned how to use the Db2 FETCH clause to limit the number of rows returned by a query.

  • Was this tutorial helpful ?
  • YesNo
Previous Db2 LIMIT
Next Db2 Alias

Getting Started

  • What is Db2
  • Installing Db2 Database Server
  • Db2 Sample Database
  • Creating a Db2 Sample Database
  • Connecting to a Db2 Database
  • Interacting with Db2 using SQL Developer

Data Manipulation

  • SELECT
  • ORDER BY
  • WHERE
  • SELECT DISTINCT
  • AND
  • OR
  • BETWEEN
  • LIKE
  • IN
  • LIMIT
  • FETCH
  • Join
  • INNER JOIN
  • LEFT JOIN
  • RIGHT JOIN
  • FULL OUTER JOIN
  • Self-Join
  • CROSS JOIN
  • GROUP BY
  • Subquery
  • HAVING
  • UNION
  • INTERSECT
  • EXCEPT
  • Common Table Expression or CTE
  • INSERT
  • INSERT Multiple Rows
  • INSERT INTO SELECT
  • UPDATE
  • DELETE

Managing Database Objects

  • CREATE TABLE
  • Identity Columns
  • ALTER COLUMN
  • ADD COLUMN
  • DROP COLUMN
  • DROP TABLE
  • TRUNCATE TABLE
  • RENAME TABLE

Db2 Constraints

  • Primary Key
  • Foreign Key
  • DEFAULT
  • NOT NULL
  • CHECK
  • UNIQUE

Db2 Data Types

  • Integer
  • Decimal
  • VARCHAR
  • CHAR
  • DATE
  • TIME
  • TIMESTAMP

Useful Functions & Expressions

  • CAST
  • CASE Expression
  • COALESCE

About db2tutorial.com

The db2tutorial.com website provides you with a comprehensive IBM DB2 tutorial with many practical examples and hands-on sessions.

Recent Tutorials

  • Db2 Functions
  • Db2 RANK
  • Db2 ROW_NUMBER
  • Db2 Window Functions
  • What is Db2

Site Links

  • Home
  • About Us
  • Contact Us
  • Privacy Policy
Copyright © © 2021 by www.db2tutorial.com. All Rights Reserved.