The Rank Function in MySQL

In this tutorial, we will introduce how to use the rank function in MySQL.

MySQL has a host of window functions. One of these is the rank function in MySQL.

Many organizations and businesses have a requirement such that they need to rank the data in their rows based on a particular condition. The rank function can be useful to get this done.

For example, in a class, if a teacher decides to rank their students based on their marks categorized by their subjects, they can use the rank function in MySQL. This function can rank students individually based on their performance to perform data analysis.

Use the Rank Function to Rank Data in MySQL

Let us understand how this function works.

Before we begin, we create a dummy dataset to work with. Here we create a table, person, and a few rows.

CREATE TABLE person (id int, first_name varchar(20), age int, gender char(1));

-- insert rows to the table student_details
INSERT INTO person VALUES (1, 'Bob', 25, 'M'),
(2, 'Jane', 20, 'F'),
(3, 'Jack', 30, 'M'),
(4, 'Bill', 32, 'M'),
(5, 'Nick', 22, 'M'),
(6, 'Kathy', 18, 'F'),
(7, 'Steve', 36, 'M'),
(8, 'Anne', 25, 'F'),
(9, 'Kamal', 25, 'M'),
(10, 'Saman', 32, 'M');

To view the entries in the data, we use the following code.

SELECT * FROM person;

The above code would give the following output.

id	first_name  	age 	gender
1	Bob				25		M
2	Jane			20		F
3	Jack			30		M
4	Bill			32		M
5	Nick			22		M
6	Kathy			18		F
7	Steve			36		M
8	Anne			25		F
9	Kamal			25		M
10	Saman			32		M

After the table is set up, we can rank data as follows.

Let us rank the people in our dataset by ordering them according to their age and partitioning them based on their gender. This operation can be done using the following query.

SELECT RANK() OVER (Partition by Gender ORDER BY Age) AS `Partition by Gender`,
  first_name as name,
  Age,
  Gender
FROM person;

In the query, the table named person will help us rank data such that all the people are partitioned by gender and are ordered in the ascending order of their ages.

The output of the query can be illustrated as follows.

id	name  	age 	gender
1	Kathy	18		F
2	Jane	20		F
3	Anne	25		F
1	Nick	22		M
2	Bob		25		M
2	Kamal	25		M
4	Jack	30		M
5	Bill	32		M
5	Saman	32		M
7	Steve	36		M

As we can see above, the data is sorted in age and segregated based on gender.

We have the alias name with the AS keyword in MySQL to increase the program’s readability.

Therefore, with the help of the RANK function and Partition by clauses, we can efficiently rank the data and order them as per our need in MySQL.