Ranking Functions

MySQL uses a ranking function that allows us to rank each row of a partition in the databases. The ranking functions are also a sub-part of a window function in MySQL. The ranking functions in MySQL can be used with the following clauses:

• They always work with the OVER()
• They assign a rank to each row based on the ORDER BY
• They assign a rank to each row in the sequential order.
• They always assign a rank to rows, starting with one for each new partition.

NOTE: It is to be noted that MySQL provides support for the ranking and window functions since version 8.0.

MySQL supports the following three types of ranking functions:

1. Dense Rank
2. Rank
3. Percent Rank

Now, we are going to discuss each ranking functions in detail:

MySQL dense_rank()

It is a function that assigns a rank for every row within a partition or result set without any gaps. The rank of rows is always assigned in consecutive order (increased by one from the previous row). Sometimes you will get a tie between the values, then the dense_rank will assign it with the same rank, and its next rank will be its next consecutive number.

The following are the syntax of dense_rank():

SELECT column_name   

DENSE_RANK() OVER (  

    PARTITION BY expression  

    ORDER BY expression [ASC|DESC])  

AS 'my_rank' FROM table_name;

In the above syntax, the PARTITION BY clause partition the result set return by FROM clause, and then the dense_rank function applied on each partition. Next, the ORDER BY clause applies to each partition to specify the order of rows.

Example 1

Let us understand how MySQL dense_rank() function works. So, first, create a table that contains the following data:

Table: employees

This statement uses the dense_rank() function for assigning the rank value for each row.

SELECT emp_id, emp_name, city, emp_age,  

DENSE_RANK() OVER (ORDER BY emp_age) dens_rank  

FROM employees;

After executing the above statement, we will get the following output:

Example 2

Let us see another example that divides the result set into partitions. The following statement uses dense_rank() function to assign the value on each row and divide the result set into partition using emp_age:

SELECT emp_id, emp_name, city, emp_age,  

DENSE_RANK() OVER (PARTITION BY emp_age ORDER BY city) dens_rank  

FROM employees;

After the successful execution of the above query, we will get the following output:

MySQL rank()

It is a function that assigns a rank for every row within a partition or result set with gaps. The rank of rows is always not-assigned in a consecutive order (i.e., increased by one from the previous row). Sometimes you will get a tie between the values, then the rank() function will assign it with the same rank, and the next rank value will be its previous rank plus a number of duplicate numbers.

The following are the syntax of rank():

SELECT column_name   

RANK() OVER (  

    PARTITION BY expression  

    ORDER BY expression [ASC|DESC])  

AS 'my_rank' FROM table_name;

In the above syntax, the PARTITION BY clause partition the result set return by FROM clause, and then the rank() function applies on each partition and re-initialized when the partition boundary crosses other partition. Next, the ORDER BY clause applies on each partition to sorts the rows based on one or more columns names.

Let us take a table that we have created previously and see the working of rank() function in MySQL with different examples.

Table: employees

Example 1

This statement uses the rank() function for assigning the rank value for each row.

SELECT emp_id, emp_name, city, emp_age,  

RANK() OVER (ORDER BY emp_age) my_rank  

FROM employees;

The above query will give the following output:

Example 2

Let us see another example that divides the result set into partitions. The following statement uses the rank() function to assign the value on each row and divide the result set into partition using emp_age and sorts them based on emp_id:

SELECT *,  

RANK() OVER (PARTITION BY emp_age ORDER BY emp_id) my_rank  

FROM employees;

Executing the above statement, we will get the following output:

MySQL percent_rank()

It is a function that calculates a percentile rank (relative rank) for rows within a partition or result set. This function returns a number from a range of values between 0 and 1.

The following are the syntax of percent_rank():

SELECT column_name   

PERCENT_RANK() OVER (  

    PARTITION BY expression  

    ORDER BY expression [ASC|DESC])  

AS 'my_rank' FROM table_name;

For a specified row, this function calculates the rank by using the following formula:

(rank-1) / ( total_rows-1)  

Here,

rank: It is the rank of each row returns by rank() function.

total_rows: It represents the total number of rows present in the partition.

NOTE: It is to make sure that when you use this function, you must have to use the ORDER BY clause. Otherwise, all rows are considered duplicates and assigned the same rank, which is 1.

Let us create a table “students” that contains the following data and see the working of percent_rank() function in MySQL.

Table: students

Example 1

This statement uses the percent_rank() function for calculating the rank value for each row order by marks column.

 SELECT stud_id, stud_name, subject, marks,  

PERCENT_RANK() OVER (PARTITION BY subject ORDER BY marks) my_rank  

FROM students; 

The above query will give the following output:

To see how the above formula works, consider the following query:

 SELECT stud_id, stud_name, subject, marks, rank()   

OVER ( partition by subject order by marks )-1   

AS 'rank-1', count(*) over (partition by subject)-1  

AS 'total_rows-1',   

PERCENT_RANK() OVER (PARTITION BY subject ORDER BY marks) my_rank  

FROM students; 

It will give the following output: