MySQL DENSE_RANK Function

 MySQL DENSE_RANK Function

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

Introduction to MySQL DENSE_RANK function

The DENSE_RANK() is a window function that assigns a rank to each row within a partition or result set with no gaps in ranking values.

The rank of a row is increased by one from the number of distinct rank values that come before the row.

The syntax of the DENSE_RANK() the function is as follows:

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

In this syntax:

• First, the PARTITION BY clause divides the result sets produced by the FROM clause into partitions. The DENSE_RANK() the function is applied to each partition.
• Second, the ORDER BY  the clause specifies the order of rows in each partition on which the DENSE_RANK() function operates.

If a partition has two or more rows with the same rank value, each of these rows will be assigned the same rank.

Unlike the RANK() function, the DENSE_RANK() the function always returns consecutive rank values.

Suppose we have a table t with some samples data 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 DENSE_RANK() function to assign a rank to each row:

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

Here is the output:

MySQL DENSE_RANK() function example

We will use the sales table created in the window function tutorial for the demonstration.

The following statement uses the DENSE_RANK() function to rank the sales employees by sale amount.

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

The output is as follows:

In this example:

• First, the PARTITION BY clause divided the result sets into partitions using fiscal year.
• Second, the ORDER BY clause specified the order of the sales employees by sales in descending order.
• Third, the DENSE_RANK() the function is applied to each partition with the order of the row specified by the ORDER BY clause.

In this tutorial, you have learned how to use the MySQL DENSE_RANK() function to rank rows in each partition of a result set.