In this tutorial, we will see what is the RANK() function, how it works, how to use it and what is the difference between the RANK() function and the dense_rank() function. So, let’s get started!
Introduction to MySQL RANK() Function
The RANK() function is similar to the DENSE_RANK() function but works slightly different. Instead of assigning contiguous rank numbers, it skips the ranking numbers if peers are found. For example, if the first and second rows have the same values, then the RANK() function will assign 1 to both rows and 3 to the third row.
Moreover, it can be used with the partition to assign the rankings to the rows within the partitions. For a new partition, ranking starts again with rank 1.
The RANK() function is supported in the MySQL version 8.0 and onwards.
For more information about the DENSE_RANK() function, you can read our detailed guide here.
As mentioned earlier, the RANK() function doesn’t assign consecutive rankings to the peer groups.
We can use the ORDER BY clause to sort the rows. It can also be clubbed with the PARTITION to create the partitions.
If you don’t use the ORDER BY clause, all rows are considered peers.
That’s enough theory. Let’s see the syntax of the RANK() function.
MySQL RANK() Function Syntax
RANK() OVER (
PARTITION BY <expression>[{,<expression>...}]
ORDER BY <expression> [ASC|DESC], [{,<expression>...}]
)Code language: SQL (Structured Query Language) (sql)Here,
The PARTITION BY clause will create the partitions. You can skip the partition clause.
The ORDER BY clause sorts the records in the partitions. If no partition is created, it will consider the whole table as a single partition and sort the records.
Note that, the first and last rank numbers assigned by the MySQL RANK() function are always 1 and the number of rows in the partition. For example, if there are 5 rows in the partition then the rank for the first row will be 1 and for the last row, it will be 5.
Let’s now take an example to understand the working of the MySQL RANK() function.
MySQL RANK() Function Example
Let’s create a table first and insert some values into it.
We will create a table to store the names and marks of students from different divisions.
CREATE TABLE marks(
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
class VARCHAR(1),
marks INT
);Code language: SQL (Structured Query Language) (sql)INSERT INTOmarks(name, class, marks) VALUES
('John', 'A', 90), ('Peter', 'A', 80),('Amy', 'B', 70),('Bob', 'B', 60),
('Cindy', 'E', 50),('David', 'C', 40),('Eve', 'D', 30),('Frank', 'D', 70),
('Grace', 'D', 50),('Harry', 'E', 40),('Irene', 'A', 80),('Jack', 'E', 42),
('Kate', 'E', 85),('Linda', 'C', 75),('Mary', 'A', 96),('Nancy', 'A', 83),
('Olivia', 'B', 71),('Peter', 'D', 90),('Quinn', 'E', 40),('Robert', 'A', 30),
('Sally', 'B', 80),('Tom', 'B', 73),('Ursula', 'B', 80),('Victor', 'E', 97);Code language: SQL (Structured Query Language) (sql)Let’s display the table data to check if the values are correctly inserted.
SELECT * FROM marks;Code language: SQL (Structured Query Language) (sql)
Perfect!
Now we will write a query using the RANK() function to assign the rankings to the students from all divisions. Note that, if two or more students have the same marks, then they will get the same ranking. However, the next individual or a group of students having the same marks will not get the immediate ranking to the previous rank.
SELECT id, name, class, marks,
RANK() OVER(ORDER BY marks DESC) as studs_rank
FROM marks; Code language: SQL (Structured Query Language) (sql)
As you can see here- we didn’t get the consecutive ranking for the next immediate groups. That’s how the RANK() function works.
Here we have ranked the students with the highest marks on the top. We can also rank them by lowest first order using the ASC order.
SELECT id, name, class, marks,
RANK() OVER(ORDER BY marks ASC) as studs_rank
FROM marks; Code language: SQL (Structured Query Language) (sql)
As you can see, ranks are assigned in the lowest-first fashion. Now, we will write a query to assign the department-wise rankings to the students.
For that, we will create a partition by the class column value and sort the marks in descending order so that students with the highest marks get the top rank.
SELECT id, name, class, marks,
RANK() OVER(PARTITION BY CLASS ORDER BY marks DESC) as studs_rank
FROM marks; Code language: SQL (Structured Query Language) (sql)
As you can see in the output above, the partitions are highlighted and the rankings are assigned to the class partitions.
Finally, we will write a query to see the live difference between the RANK(), DENSE_RANK() and ROW_NUMBER() functions in one place.
SELECT id, name, class, marks,
RANK() OVER w as studs_rank,
DENSE_RANK() OVER w as stud_dense_rank,
ROW_NUMBER() OVER w as stud_row_number
FROM marks
WINDOW w AS (ORDER BY marks);Code language: SQL (Structured Query Language) (sql)
Summary
In this tutorial, we learned-
- What is the RANK() window function
- How the RANK() function assigns the rankings
- How is it different from the DENSE_RANK() function
- Syntax of the RANK() function
- Examples of the RANK() function
- Example of RANK() vs DENSE_RANK() vs ROW_NUMBER() function
Reference
MySQL official documentation on the RANK() function.