Updated May 12, 2023
Introduction to DB2 row_number
DB2 ROW_NUMBER is a function provided by IBM for generating a sequential number that can begin from 1 and continuously show an iterating value in the column name specified. Analytical Processing (OLAP) and is itself a window function. It is only possible because of this function that we don’t need to reiterate the data of the table again and again to induce a functionality of getting incremental values. It can be used in many real-time applications. One of the most frequent and popular usages of this function is pagination. In this article, we will see how we can use the ROW_NUMBER function, its syntax, and examples.
Syntax:
ROW_NUMBER () OVER ( [clause for partitions] clause to order the data based on certain column(s) value)
The ROW_NUMBER() function will generate a column that will contain the integer values beginning from the value assigned to the first row. One of the most useful but optional features in this syntax is the usage of partitions which can help us to assign the sequential number based on the groups or partitions of the data.
In the above syntax, the clause for partition is optional in nature. If we use this function, the result set rows are firstly divided into multiple groups based on the partition key and then the ROW_NUMBER function is implemented on each of the grouped data. By default, if we don’t specify the clause for partitions in our query statement then all the rows of the result set are considered as a single partition or group and then the function is implemented for it. The clause for the partition of syntax has the following syntax for specifying the partition key –
PARTITION BY [key1, key2, ….keyn]
Where key1, key2, ….keyn can be the names of the columns or any expression value on the basis of which we want to segregate the result.
The clause for ordering is used to order the data that is generated in the result set based on one or more order keys that are specified. The ORDER BY clause works individually for each partition. We can either specify the ordering to be done in ascending or descending manner depending on our requirement by using ASC or DESC at the end of the ORDER BY clause. By default, if we don’t specify either of them the ordering is done in ascending mode. We can also specify whether we have to display all the NULL values first or last in the result set by using the NULLS FIRST and NULLS LAST statements in the syntax of the clause for the order. The syntax of the order clause is shown below –
ORDER BY sort_exp1 [,sort_exp2, ..., sort_expn]
[ASC | DESC]
[NULLS LAST | FIRST]
In the above syntax, the sort_exp1 [,sort_exp2, …, sort_expn] is the list of expressions like columns or collective use it number which is different with respect to each row in the result set. The specification of ASC or DEC is optional in nature to specify the order of the column values and result in ascending or descending order explicitly. By default, it’s done in ascending order. Many times, there is a requirement to display the data in the format where all the NULL values are displayed in the top at first in the result or all the result set is ordered in a way where all the rows with NULL values in it are displayed at the end. This is done by using the NULLS FIRST or NULLS LAST respectively.
Examples of DB2 row_number
Let us take one table named Sales_Customers which is created by using the following query statement –
CREATE TABLE Sales_Customers
( customer_id NUMBER(6)
, f_name VARCHAR2(20)
, l_name VARCHAR2(25)
, email_id VARCHAR2(40)
, mobile_number VARCHAR2(20)
, purchase_date VARCHAR2(20)
, store_id VARCHAR2(20)
, bill_amount NUMBER(8,2)
, salesman_id NUMBER(6)
, department_id NUMBER(4)
) ;
The content of the table can be seen by using the following query statement –
SELECT * FROM Sales_Customers;
Which gives the following output –
As it can be seen the table has 14 rows in it and all are sorted based on the primary key customer_id when they are retrieved. We can assign the row number value as a pseudo column to see which row is on which number and how many rows are there by using the window function named ROW_NUMBER and using the following query statement for the same –
SELECT
customer_id,
f_name,
ROW_NUMBER() OVER () AS row_number
FROM
Sales_Customers;
The output of the above query statement is as shown below –
If we want to order the data based on the amount of purchase done by that customer. We can do that by using the following query statement and mentioning the column name in the order by clause of the ROW_NUMBER() function –
SELECT
customer_id,
bill_amount,
f_name,
ROW_NUMBER() OVER (
ORDER BY bill_amount
) AS row_number
FROM
Sales_Customers;
The output of the above query is as shown below –
If we want to do the pagination based on a certain column then
WITH big_customers AS (
SELECT
customer_id,
f_name,
bill_amount,
ROW_NUMBER() OVER (
ORDER BY bill_amount
) row_number
FROM Sales_Customers
)
SELECT
*
FROM
big_customers
WHERE
row_number > 7 AND
row_number <= 15
In big_customers the order by clause made sure that the customers are ordered and sorted based on the amount of bill of customers. Further, the ROW_NUMBER got applicable to all the resulted data having bill amounts arranged in ascending order, hence unique sequential numbers got assigned to each row, and by using the where clause we filtered out all the customers whose row numbers were in the range of 7 to 15 exclusive.
Suppose that we want the top 3 customers from the sales_customers table then we can do that by applying the partition on customer_id and setting the result set in the descending order of the bill amounts of the customer by using the following query statement –
WITH top_customers AS (
SELECT
customer_id,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY bill_amount DESC
) row_number,
bill_amount,
f_name
FROM
Sales_Customers
WHERE
customer_id IS NOT NULL
)
SELECT
*
FROM
top_customers
WHERE
+
which gives the following output –
Conclusion
We can make use of the ROW_NUMBER function to generate a pseudo column in the result set which is sequential in nature and can be used to assign the unique incremental values to rows.
Recommended Articles
This is a guide to DB2 row_number. Here we discuss the Introduction, syntax, and examples with code implementation. You may also have a look at the following articles to learn more –
