DB2 Tutorial

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

Db2 ROW_NUMBER

Summary: in this tutorial, you will learn how to use the Db2 ROW_NUMBER() function to assign a unique sequential integer to each row in a result set.

Introduction to Db2 ROW_NUMBER() function

The Db2 ROW_NUMBER() is a window function that assigns each row in a result set a unique sequential integer. The first sequential number is one assigned to the first row.

Here is the syntax of the ROW_NUMBER() function:

ROW_NUMBER() OVER ( [partition_clause] order_by_clause)

partition_clause

The partition_clause is optional. It divides rows into multiple groups (or partitions) to which the function is applied. If you skip the partition clause, the function will treat the whole result set as a single partition.

The following shows the syntax of the PARTITION BY clause:

PARTITION BY expression1 [,expression2,...]

 order_by_clause

The order_by_clause specifies the order of rows in each partition according to one or more sort keys. The order_by_clause sorts rows in ascending order (ASC) by default if you don’t specify either ASC or DESC.

To specify the order of NULL values, you use NULLS FIRST or NULLS LAST option. The NULLS FIRST places theNULL values before other non-NULL values while the NULLS LAST places the NULL values after other non-NULL values.

The following shows the syntax of the ORDER BY clause:

ORDER BY sort_expression1 [,sort_expression2, ...] [ASC | DESC] [NULLS FIRST | LAST]

Db2 ROW_NUMBER() function examples

We’ll use the books table from the sample database for the demonstration.

1) Simple Db2 ROW_NUMBER() function example

The following example adds a unique sequential number to each row of the result set:

SELECT book_id, title, ROW_NUMBER() OVER ( ORDER BY published_date ) row_num FROM books;

Here is the partial output:

db2 row_number example

2) Using the Db2 ROW_NUMBER() function for pagination example

Suppose that you have to display books by pages, 10 books per page. To show the books that belong to the second page, you can use the ROW_NUMBER() function as follows:

  • First, add a sequential integer to each row in the result set.
  • Then, select books that have row numbers from 11 to 20.

The following query illustrates the steps:

WITH cte_books AS ( SELECT book_id, title, ROW_NUMBER() OVER ( ORDER BY published_date ) row_num FROM books ) SELECT * FROM cte_books WHERE row_num > 10 AND row_num <= 20;

Output:

db2 row_number for pagination

3) Using Db2 ROW_NUMBER() function for the top-N query example

The following query returns the top 2 books in terms of ratings for each publisher:

WITH cte_books AS ( SELECT publisher_id, ROW_NUMBER() OVER ( PARTITION BY publisher_id ORDER BY rating DESC ) row_num, book_id, rating, title FROM books WHERE publisher_id IS NOT NULL ) SELECT * FROM cte_books WHERE row_num >= 3;

Partial Output:

db2 row_number over partition example

How it works.

In the CTE:

  • The PARTITION BY clause divided books by the publisher id.
  • The ORDER BY clause sorted books by ratings from high to low.
  • The ROW_NUMBER() assigned a unique sequential integer to each row in each partition.

The outer query selects the top 3 books by specifying a condition in the WHERE clause.

In this tutorial, you have learned how to use the Db2 ROW_NUMBER() function to assign a unique sequential integer to each row in a result set.

  • Was this tutorial helpful ?
  • YesNo
Next Db2 RANK

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.