DENSE_RANK
Returns the rank of a row in a window partition, like RANK, but without gaps between places.
MySQL 8.1
DENSE_RANK() OVER ([PARTITION BY ...] [ORDER BY ...])Parameters
- PARTITION BYoptional
- Columns that split rows into partitions, ranks are computed in each one separately
- ORDER BYoptional
- Order of rows that defines the place; without it all rows have rank 1
Return value
An integer (BIGINT UNSIGNED), the place of the row; equal values share a place and the next place is one higher: 1, 2, 2, 3
Examples
DENSE_RANK and RANK by status
MySQL 8.1
SELECT member_name,
status,
RANK() OVER (
ORDER BY status
) AS status_rank,
DENSE_RANK() OVER (
ORDER BY status
) AS status_dense_rank
FROM FamilyMembers;Most expensive products in each category
MySQL 8.1
SELECT category,
name,
price
FROM (
SELECT category,
name,
price,
DENSE_RANK() OVER (
PARTITION BY category
ORDER BY price DESC
) AS price_rank
FROM products
) AS ranked
WHERE price_rank = 1;Details
Handy for finding the N-th largest value: rows with DENSE_RANK() = 2 contain the second value, even if the first one occurs several times. Filter by rank in the outer query: window functions cannot be used in WHERE of the same query.