MySQL RANK Function

 MySQL RANK Function

Summary: in this tutorial, you will learn about the MySQL RANK() function and how to apply it to assign the rank to each row within the partition of a result set.

Note that MySQL has been supporting the RANK() function and other window functions since version 8.0

Introduction to MySQL RANK() function

The RANK() a function assigns a rank to each row within the partition of a result set. The rank of a row is specified by one plus the number of ranks that come before it.

The following shows the syntax of the RANK() function:

RANK() OVER (
    PARTITION BY <expression>[{,<expression>...}]
    ORDER BY <expression> [ASC|DESC], [{,<expression>...}]
)

In this syntax:

• First, the PARTITION BY clause divides the result sets into partitions. The RANK() function is performed within partitions and re-initialized when crossing the partition boundary.
• Second, The ORDER BY clause sorts the rows within a partition by one or more columns or expressions.

Unlike the ROW_NUMBER() function, the RANK() function does not always return consecutive integers.

Suppose we have a sample table as follows:

CREATE TABLE t (
    val INT
);

INSERT INTO t(val)
VALUES(1),(2),(2),(3),(4),(4),(5);


SELECT * FROM t;

The following statement uses the RANK() function to assign a rank to each row from the result set in the t table:

SELECT
    val,
    RANK() OVER (
        ORDER BY val
    ) my_rank
FROM
    t;

Here is the output:

As you can see, the second and third rows have the same ties so they receive the same rank 2.

The fourth row has rank 4 because the RANK() function skips the rank 3.

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:

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;

The following picture shows the data of the sales table:

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

SELECT
    sales_employee,
    fiscal_year,
    sale,
    RANK() OVER (PARTITION BY
                     fiscal_year
                 ORDER BY
                     sale DESC
                ) sales_rank
FROM
    sales;

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:

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 <=3;

Here is the output:

In this example:

• First, we used a common table expression (CTE) to get the order number, order year, and rank. To rank orders by order value in each year, we used the RANK() a 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 to 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.