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

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

Basic syntax:

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

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

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

Practice #1:

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

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

Let's create exam_result table to show you how to rank 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 RANK() query to get students ranked by exam results in each class.

SELECT StudentName,
	ClassName,
	StudentMark,
	RANK() 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, they both receive the same rank value, but unlike DENSE_RANK() function, RANK() function does NOT assign consecutive ranks.

In Class A, both Gary and Tom are assigned the same rank value 2 as they have the same mark, but John receives rank value 4 where 3 is skipped.

9 records returned:

Practice #2:

Query below does not use PARTITION BY clause which literally treats all classes as one group, so it ranks all students based on their marks regardless of which class they belong to.

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

9 records returned:

Practice #3:

The query below ranks products by the number of units in stock in each product category.

select p.ProductName,
	c.CategoryName,
    p.UnitsInStock,
    RANK() 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 ranks customers by the total sales amount within each order date. Note that the ORDER BY clause within the brackets of the RANK() 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,
    RANK() 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, sales_rank;

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 DENSE_RANK() function

12. MySQL ROW_NUMBER() 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