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 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 which come before the row.

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

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

Code language: SQL (Structured Query Language) (sql)

In this syntax:

First, the PARTITION BY clause divides the result sets produced by the FROM clause into partitions. The DENSE_RANK() function is applied to each partition.
Second, the ORDER BY 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() 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;

Code language: SQL (Structured Query Language) (sql)

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;

Code language: SQL (Structured Query Language) (sql)

Here is the output: