This is in contrast with RANK() and DENSE_RANK function.

Please note that ROW_NUMBER() has become available since MySQL version 8.0. If you get syntax error when running the ROW_NUMBER() function on this page, check your MySQL version (SELECT VERSION ()), most likely you're running an older version of MySQL.

Basic syntax:

SELECT column1,
	ROW_NUMBER() OVER (PARTITION BY... ORDER BY...) as rank
FROM table1;

MySQL ROW_NUMBER() function can be viewed as "numbering within a group". So the best way to understand MySQL ROW_NUMBER() function is to ask you three questions.

1. Number who? (column1)
2. Within what group? (PARTITION BY)
3. Number by what? (ORDER BY)

Practice #1:

Suppose you teach students in two classes. After final exam, you need to assign a unique number to each student in each class by their marks in the exam. So the answers to the 3 questions are:

1. Number students (SELECT StudentName)
2. Within each class (PARTITION BY ClassName)
3. Number by mark (ORDER BY StudentMark DESC)

Let's create exam_result table to show you how to assign unique numbers to students in this scenario.

-- Create table
CREATE TABLE exam_result
(
   StudentName varchar(20) NOT NULL,
   ClassName varchar(20) NOT NULL,
   StudentMark tinyint NOT NULL
);

Insert data to exam_result table.

-- Add sample data
insert into exam_result values('Anne','Class A',92),
('Gary','Class A',85),
('Tom','Class A',85),
('John','Class A',76),
('Jerry','Class B',95),
('Nick','Class B',91),
('Joe','Class B',88),
('Ted','Class B',88),
('Bob','Class B',82);

Here is the ROW_NUMBER() query to get students uniquely numbered by exam results in each class.

SELECT StudentName,
	ClassName,
	StudentMark,
	ROW_NUMBER() OVER (PARTITION BY ClassName ORDER BY StudentMark DESC) as StudentRank
FROM exam_result;

Please note that when a student has the same mark as another student, each of them receives a distinct row number. Furthermore, unlike RANK() and DENSE_RANK(), ROW_NUMBER() function assigns consecutive, non-duplicated numbers.

In Class A, both Gary and Tom are assigned different numbers even though they have the same mark, and John receives number 4 which is consecutively incremented from 3.

9 records returned:

Practice #2:

Query below does not use PARTITION BY clause which literally treats all classes as one group, so it assigns a unique and incremented number to each student based on their marks regardless of which class they belong to.

SELECT StudentName,
	ClassName,
	StudentMark,
	ROW_NUMBER() OVER (ORDER BY StudentMark DESC) as StudentRank
FROM exam_result;

9 records returned:

Practice #3:

The query below assigns unique numbers to products by the number of units in stock in each product category.

select p.ProductName,
	c.CategoryName,
    p.UnitsInStock,
    ROW_NUMBER() OVER (PARTITION BY c.CategoryName ORDER BY p.UnitsInStock) as stock_rank
from products as p 
join categories as c on p.CategoryID=c.CategoryID
order by c.CategoryName, p.UnitsInStock, stock_rank;

77 records returned:

Practice #4:

This query numbers customers by the total sales amount within each order date. Note that the ORDER BY clause within the brackets of the ROW_NUMBER() OVER is a calculated column by using SUM function and GROUP BY for aggregation.

select b.CustomerID, 
	b.OrderDate,
	sum(a.UnitPrice * (1 - a.Discount) * a.Quantity) as Sales,
    ROW_NUMBER() OVER (PARTITION BY b.OrderDate ORDER BY sum(a.UnitPrice * (1 - a.Discount) * a.Quantity)) as sales_rank
from order_details as a
join orders as b on a.OrderID=b.OrderID
group by b.CustomerID, 
	b.OrderDate
order by b.OrderDate;

823 records returned:

Happy Coding!

Other tutorials in this category

1. Using String Functions, Part 1

2. Using String Functions, Part 2

3. Using String Functions, Part 3

4. Using MySQL Date and Time Functions, Part 1

5. Using MySQL Date and Time Functions, Part 2

6. Using MySQL Date and Time Functions, Part 3

7. Using MySQL Control Flow Functions

8. Using MySQL Conversion Functions

9. Using MySQL Comparison Functions

10. Using Aggregate Functions in MySQL

11. MySQL RANK() function

12. MySQL DENSE_RANK() function

13. Compare RANK(), DENSE_RANK(), and ROW_NUMBER() function

14. Using LIMIT and OFFSET to Page Thru Query Result in MySQL

15. Using LIMIT, SQL_CALC_FOUND_ROWS and FOUND_ROWS() to Page Thru Query Result in MySQL

16. Use LEAST function to find the SMALLEST value from multiple arguments

17. How to make case-sensitive comparison in MySQL

18. Use GREATEST function to find the LARGEST value from multiple arguments

19. MySQL MID() function with examples

20. MySQL LOCATE() function with examples

21. MySQL SUBSTR() function with examples

22. MySQL POSITION() function with examples

23. How to use MySQL REPLACE function in SELECT and UPDATE queries

Back to Tutorial Index Page