{"id":1273,"date":"2019-02-21T10:23:21","date_gmt":"2019-02-21T03:23:21","guid":{"rendered":"http:\/\/www.sqlservertutorial.net\/?page_id=1273"},"modified":"2024-07-23T22:02:24","modified_gmt":"2024-07-23T15:02:24","slug":"sql-server-row_number-function","status":"publish","type":"page","link":"https:\/\/www.sqlservertutorial.net\/sql-server-window-functions\/sql-server-row_number-function\/","title":{"rendered":"SQL Server ROW_NUMBER Function"},"content":{"rendered":"\n

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

Introduction to SQL Server ROW_NUMBER() function #<\/a><\/h2>\n\n\n\n

The ROW_NUMBER()<\/code> is a window function<\/a> that assigns a sequential integer to each row within the partition of a result set. The row number starts with 1 for the first row in each partition.<\/p>\n\n\n\n

The following shows the syntax of the ROW_NUMBER()<\/code> function:<\/p>\n\n\n

ROW_NUMBER() OVER (\n    [PARTITION BY partition_expression, ... ]\n    ORDER BY sort_expression [ASC | DESC], ...\n)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Let’s examine the syntax of the ROW_NUMBER()<\/code> function in detail.<\/p>\n\n\n\n
PARTITION BY #<\/a><\/h3>\n\n\n\n
The PARTITION BY<\/code> clause divides the result set into partitions (another term for groups of rows). The ROW_NUMBER()<\/code> function is applied to each partition separately and reinitialized the row number for each partition.<\/p>\n\n\n\n
The PARTITION BY<\/code> clause is optional. If you skip it, the ROW_NUMBER()<\/code> function will treat the whole result set as a single partition.<\/p>\n\n\n\n
ORDER BY #<\/a><\/h3>\n\n\n\n
The ORDER BY<\/code> clause defines the logical order of the rows within each partition of the result set. The ORDER BY<\/code> clause is mandatory because the ROW_NUMBER()<\/code> function is order-sensitive.<\/p>\n\n\n\n
SQL Server ROW_NUMBER() function examples #<\/a><\/h2>\n\n\n\n
We’ll use the sales.customers<\/code> table from the sample database<\/a> to demonstrate the ROW_NUMBER()<\/code> function.<\/p>\n\n\n\n
\"customers\"<\/figure>\n\n\n\n
Using SQL Server ROW_NUMBER() function over a result set example #<\/a><\/h3>\n\n\n\n
The following statement uses the ROW_NUMBER()<\/code> to assign each customer row a sequential number:<\/p>\n\n\n
SELECT<\/span> \n   ROW_NUMBER() OVER<\/span> (\n\tORDER<\/span> BY<\/span> first_name\n   ) row_num,\n   first_name, \n   last_name, \n   city\nFROM<\/span> \n   sales.customers;<\/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
\"SQL<\/figure>\n\n\n\n
In this example, we skipped the PARTITION BY<\/code> clause, therefore, the ROW_NUMBER()<\/code> treated the whole result set as a single partition.<\/p>\n\n\n\n
Using SQL Server ROW_NUMBER() over partitions example #<\/a><\/h3>\n\n\n\n
The following example uses the ROW_NUMBER()<\/code> function to assign a sequential integer to each customer. It resets the number when the city changes:<\/p>\n\n\n
SELECT<\/span> \n   first_name, \n   last_name, \n   city,\n   ROW_NUMBER() OVER<\/span> (\n      PARTITION<\/span> BY<\/span> city\n      ORDER<\/span> BY<\/span> first_name\n   ) row_num\nFROM<\/span> \n   sales.customers\nORDER<\/span> BY<\/span> \n   city;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The following picture shows the partial output:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
In this example, we used the PARTITION BY<\/code> clause to divide the customers into partitions by city. The row number was reinitialized when the city changed.<\/p>\n\n\n\n
Using SQL Server ROW_NUMBER() for pagination #<\/a><\/h3>\n\n\n\n
The ROW_NUMBER()<\/code> function is useful for pagination in applications. For example, you can display a list of customers by page, where each page has 10 rows.<\/p>\n\n\n\n
The following example uses the ROW_NUMBER()<\/code> to return customers from rows 11 to 20, which is the second page:<\/p>\n\n\n
WITH<\/span> cte_customers AS<\/span> (\n    SELECT<\/span> \n        ROW_NUMBER() OVER<\/span>(\n             ORDER<\/span> BY<\/span> \n                first_name, \n                last_name\n        ) row_num, \n        customer_id, \n        first_name, \n        last_name\n     FROM<\/span> \n        sales.customers\n) SELECT<\/span> \n    customer_id, \n    first_name, \n    last_name\nFROM<\/span> \n    cte_customers\nWHERE<\/span> \n    row_num > 10<\/span> AND<\/span> \n    row_num <= 20<\/span>;\n<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The output is as follows:<\/p>\n\n\n\n
\"SQL<\/figure>\n\n\n\n
In this example:<\/p>\n\n\n\n