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 BYclause divides the result sets produced by theFROMclause into partitions. TheDENSE_RANK()function is applied to each partition. - Second, the
ORDER BYclause specifies the order of rows in each partition on which theDENSE_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
);VALUES(1),(2),(2),(3),(4),(4),(5);
*
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:
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;
Code language: SQL (Structured Query Language) (sql)
The output is as follows:
In this example:
- First, the
PARTITION BYclause divided the result sets into partitions using fiscal year. - Second, the
ORDER BYclause specified the order of the sales employees by sales in descending order. - Third, the
DENSE_RANK()function is applied to each partition with the rows order specified by theORDER BYclause.
In this tutorial, you have learned how to use the MySQL DENSE_RANK() function to rank rows in each partition of a result set.