MySQL Ranking Functions

If your interested to learn about the MySQL Number format function

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.

Types of Ranking

There are 3 types of ranking functions supported in MySQL-

1. dense_rank():
   This function will assign rank to each row within a partition without gaps. Basically, the ranks are assigned in a consecutive manner i.e if there is a tie between values then they will be assigned the same rank, and next rank value will be one greater then the previous rank assigned.
2. rank():
   This function will assign rank to each row within a partition with gaps. Here, ranks are assigned in a non-consecutive manner i.e if there is a tie between values then they will be assigned same rank, and next rank value will be previous rank + no of peers(duplicates).
3. percent_rank():
   It returns the percentile rank of a row within a partition that ranges from 0 to 1. It tells the percentage of partition values less than the value in the current row, excluding the highest value.

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.43.3M703Hello Java Program for Beginners. 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 , 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() 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:

More Examples

MySQL RANK() function example

Let’s use the sales table created in the window function tutorial for the demonstration. If you have not created the sales table yet, here is the script:

<code>CREATE TABLE IF NOT EXISTS sales(
    sales_employee VARCHAR(50) NOT NULL,
    fiscal_year INT NOT NULL,
    sale DECIMAL(14,2) NOT NULL,
    PRIMARY KEY(sales_employee,fiscal_year)
);
 
INSERT INTO sales(sales_employee,fiscal_year,sale)
VALUES('Bob',2016,100),
      ('Bob',2017,150),
      ('Bob',2018,200),
      ('Alice',2016,150),
      ('Alice',2017,100),
      ('Alice',2018,200),
       ('John',2016,200),
      ('John',2017,150),
      ('John',2018,250);
 
SELECT * FROM sales;</code><small>Code language: SQL (Structured Query Language) (sql)</small>

The following picture shows the data of the sales table:

The following statement use the RANK() function to rank the sales employees by sales amount in every year:

<code>SELECT
    sales_employee,
    fiscal_year,
    sale,
    RANK() OVER (PARTITION BY
                     fiscal_year
                 ORDER BY
                     sale DESC
                ) sales_rank
FROM
    sales;
</code><small>Code language: SQL (Structured Query Language) (sql)</small>

In this example:

• First, the PARTITION BY clause breaks the result sets into partitions by fiscal year.
• Then, the ORDER BY clause sorts the sales employees by sales in descending order.

MySQL RANK() function with CTE example

The following statement uses the RANK() function to find the top three highest valued-orders in each year:

<code>WITH order_values AS(
    SELECT 
        orderNumber, 
        YEAR(orderDate) order_year,
        quantityOrdered*priceEach AS order_value,
        RANK() OVER (
            PARTITION BY YEAR(orderDate)
            ORDER BY quantityOrdered*priceEach DESC
        ) order_value_rank
    FROM
        orders
    INNER JOIN orderDetails USING (orderNumber)
)
SELECT 
    * 
FROM 
    order_values
WHERE 
    order_value_rank &lt;=3;
</code><small>Code language: SQL (Structured Query Language) (sql)</small>

Here is the output:

In this example:

• First, we used a common table expression (CTE) to get the order number, order year, and the rank. To rank orders by order value in each year, we used the RANK() function that partitioned the rows by order year and sorted the order value in descending order.
• Then, we selected only the orders whose rank is less than or equal three.

In this tutorial, you have learned how to use the MySQL RANK() function to assign a rank to each row in a result set.