RANK
Returns the rank of a row in a window partition, that is its place in ORDER BY order.
PostgreSQL 17.5
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 places are skipped: 1, 2, 2, 4
Examples
Rank by status with gaps
PostgreSQL 17.5
SELECT member_name,
status,
RANK() OVER (
ORDER BY status
) AS status_rank
FROM FamilyMembers;Price places for each family member
PostgreSQL 17.5
SELECT family_member,
unit_price,
RANK() OVER (
PARTITION BY family_member
ORDER BY unit_price DESC
) AS price_rank
FROM Payments
ORDER BY family_member,
price_rank;Details
Without ORDER BY every row of the partition gets rank 1. Window functions do not work in WHERE, so to pick the top places, compute the rank in a subquery and filter in the outer query.