{"id":6932,"date":"2018-08-21T07:33:34","date_gmt":"2018-08-21T14:33:34","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=6932"},"modified":"2024-01-04T07:12:14","modified_gmt":"2024-01-04T14:12:14","slug":"mysql-row_number-function","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-window-functions\/mysql-row_number-function\/","title":{"rendered":"MySQL ROW_NUMBER Function"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn about the MySQL ROW_NUMBER()<\/code> function and how to use it to generate a sequential number for each row in the result set.<\/p>\n\n\n\n

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

MySQL introduced the ROW_NUMBER()<\/code> function since version 8.0. The ROW_NUMBER()<\/code> is a window function<\/a> or analytic function that assigns a sequential number to each row in the result set. The first number begins with one.<\/p>\n\n\n\n

Notice that if you use MySQL with a version less than 8.0, you can emulate some functionality of the ROW_NUMBER()<\/code> function<\/a> using various techniques.<\/p>\n\n\n\n

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

ROW_NUMBER() OVER (<partition_definition> <order_definition>)<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
partition_definition<\/h3>\n\n\n\n
The partition_definition<\/code> has the following syntax:<\/p>\n\n\n
PARTITION BY <expression>,[{,<expression>}...]<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The PARTITION BY<\/code> clause is optional. If you omit it, the entire result set is considered a partition. However, when you use the PARTITION BY<\/code> clause, each partition can be also considered as a window.<\/p>\n\n\n\n
order_definition<\/h3>\n\n\n\n
The order_definition<\/code> syntax looks like the following:<\/p>\n\n\n
ORDER BY <expression> [ASC|DESC],[{,<expression>}...]<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
MySQL ROW_NUMBER() function examples<\/h2>\n\n\n\n
Let’s use the products<\/code> table from the sample database<\/a> for the demonstration:<\/p>\n\n\n\n
\"products<\/figure>\n\n\n\n
1) Assigning sequential numbers to rows<\/h3>\n\n\n\n
The following statement uses the ROW_NUMBER()<\/code> function to assign a sequential number to each row from the products<\/code> table:<\/p>\n\n\n
SELECT<\/span> \n  ROW_NUMBER() OVER<\/span> (\n    ORDER<\/span> BY<\/span> productName\n  ) row_num, \n  productName, \n  msrp \nFROM<\/span> \n  products \nORDER<\/span> BY<\/span> \n  productName;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
Here is the output:<\/p>\n\n\n\n
\"MySQL<\/figure>\n\n\n\n
2) Finding the top N rows of every group<\/h3>\n\n\n\n
You can use the ROW_NUMBER()<\/code> function to find the top N rows for every group, for example, the top three sales employees by sales channels or the top five high-performance products by product categories.<\/p>\n\n\n\n
The following statement uses the ROW_NUMBER()<\/code> to find the top three products by product line that have the highest inventory:<\/p>\n\n\n
WITH<\/span> inventory AS<\/span> (\n  SELECT<\/span> \n    productLine, \n    productName, \n    quantityInStock, \n    ROW_NUMBER() OVER<\/span> (\n      PARTITION<\/span> BY<\/span> productLine \n      ORDER<\/span> BY<\/span> \n        quantityInStock DESC<\/span>\n    ) row_num \n  FROM<\/span> \n    products\n) \nSELECT<\/span> \n  productLine, \n  productName, \n  quantityInStock \nFROM<\/span> \n  inventory \nWHERE<\/span> \n  row_num <= 3<\/span>;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
In this example,<\/p>\n\n\n\n
The purpose of the ORDER BY<\/code> clause is to set the orders of rows. Note that this ORDER BY<\/code> clause is independent of the ORDER BY<\/a><\/code> clause of the query.<\/p>\n\n\n\n
The PARTITION BY<\/code> clause breaks the rows into smaller sets. The expression<\/code> can be any valid expression that would be used in the GROUP BY<\/a><\/code> clause. It’s possible to use multiple expressions separated by a comma (,<\/code>).<\/p>\n\n\n\n