DENSE_RANK
Returns the rank of a row in a window partition, like RANK, but without gaps between places.
PostgreSQL 17.5
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
Return value
An integer (bigint) place; equal values share a place and the next place is one higher: 1, 2, 2, 3
Examples
DENSE_RANK and RANK by status
PostgreSQL 17.5
SELECT member_name,
status,
RANK() OVER (
ORDER BY status
) AS status_rank,
DENSE_RANK() OVER (
ORDER BY status
) AS status_dense_rank
FROM FamilyMembers;Three highest prices
PostgreSQL 17.5
SELECT good,
unit_price,
price_rank
FROM (
SELECT good,
unit_price,
DENSE_RANK() OVER (
ORDER BY unit_price DESC
) AS price_rank
FROM Payments
) AS ranked
WHERE price_rank <= 3;Details
The function is handy for picking the N largest values together with ties: equal values share one place, and the next places are not skipped. Filter by rank in the outer query, since window functions do not work in WHERE.