{"id":973,"date":"2020-02-10T01:31:55","date_gmt":"2020-02-10T09:31:55","guid":{"rendered":"https:\/\/db2tutorial.com\/?page_id=973"},"modified":"2020-04-11T06:57:41","modified_gmt":"2020-04-11T14:57:41","slug":"db2-row_number","status":"publish","type":"page","link":"https:\/\/www.db2tutorial.com\/db2-window-functions\/db2-row_number\/","title":{"rendered":"Db2 ROW_NUMBER"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to use the Db2 ROW_NUMBER()<\/code> function to assign a unique sequential integer to each row in a result set.<\/p>\n\n\n\n

Introduction to Db2 ROW_NUMBER() function<\/h2>\n\n\n\n

The Db2 ROW_NUMBER()<\/code> is a window function<\/a> that assigns each row in a result set a unique sequential integer. The first sequential number is one assigned to the first row.<\/p>\n\n\n\n

Here is the syntax of the ROW_NUMBER()<\/code> function:<\/p>\n\n\n

ROW_NUMBER()\nOVER ( [partition_clause] order_by_clause)\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
partition_clause<\/h3>\n\n\n\n
The partition_clause<\/code> 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.<\/p>\n\n\n\n
The following shows the syntax of the PARTITION BY<\/code> clause:<\/p>\n\n\n
PARTITION BY expression1 [,expression2,...]\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
 order_by_clause<\/h3>\n\n\n\n
The order_by_clause<\/code> specifies the order of rows in each partition according to one or more sort keys. The order_by_clause<\/code> sorts rows in ascending order (ASC<\/code>) by default if you don’t specify either ASC<\/code> or DESC<\/code>.<\/p>\n\n\n\n
To specify the order of NULL<\/code> values, you use NULLS FIRST<\/code> or NULLS LAST<\/code> option. The NULLS FIRST<\/code> places theNULL<\/code> values before other non-NULL values while the NULLS LAST<\/code> places the NULL<\/code> values after other non-NULL values.<\/p>\n\n\n\n
The following shows the syntax of the ORDER BY<\/code> clause:<\/p>\n\n\n
ORDER BY sort_expression1 [,sort_expression2, ...] \n[ASC | DESC] \n[NULLS FIRST | LAST]\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Db2 ROW_NUMBER() function examples<\/h2>\n\n\n\n
We’ll use the books<\/code> table from the sample database<\/a> for the demonstration.<\/p>\n\n\n\n
\"\"<\/figure>\n\n\n\n
1) Simple Db2 ROW_NUMBER() function example<\/h3>\n\n\n\n
The following example adds a unique sequential number to each row of the result set:<\/p>\n\n\n
SELECT<\/span> \n    book_id, \n    title, \n    ROW_NUMBER() OVER<\/span> (\n        ORDER<\/span> BY<\/span> published_date\n    ) row_num\nFROM<\/span> \n   books;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the partial output:<\/p>\n\n\n\n
\"db2<\/figure>\n\n\n\n
2) Using the Db2 ROW_NUMBER() function for pagination example<\/h3>\n\n\n\n
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()<\/code> function as follows:<\/p>\n\n\n\n