DENSE_RANK
Returns the rank of a row in a window partition, like RANK, but without gaps between places.
PostgreSQL 17.9
DENSE_RANK()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.9
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.9
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.