Window functions
Ranking rows with ROW_NUMBER, RANK, and DENSE_RANK
One of the most common uses of window functions is assigning a position to each row.
For example, we might want to know:
12345Which is the most expensive product in each category?What are the top three orders for each customer?Where does an employee rank within their department?PostgreSQL provides several ranking window functions.
The three most important are:
123ROW_NUMBER()RANK()DENSE_RANK()ROW_NUMBER
Let's rank some products by price.
Run:
1234567891011SELECT id, name, category, price, ROW_NUMBER() OVER ( ORDER BY price DESC ) AS positionFROM productsWHERE id <= 10ORDER BY position;You should see:
123456789101112 id | name | category | price | position----+------------+----------+-------+---------- 10 | Product 10 | audio | 5.10 | 1 9 | Product 9 | garden | 5.09 | 2 8 | Product 8 | office | 5.08 | 3 7 | Product 7 | outdoor | 5.07 | 4 6 | Product 6 | home | 5.06 | 5 5 | Product 5 | audio | 5.05 | 6 4 | Product 4 | garden | 5.04 | 7 3 | Product 3 | office | 5.03 | 8 2 | Product 2 | outdoor | 5.02 | 9 1 | Product 1 | home | 5.01 | 10The important part is:
123ROW_NUMBER() OVER ( ORDER BY price DESC)ROW_NUMBER() assigns a sequential number to each row:
123451234...The window ORDER BY determines which row receives each number.
Because we used:
1ORDER BY price DESCthe most expensive row receives 1, the next receives 2, and so on.
Ranking within groups
Often we don't want one ranking across the entire result.
Instead, we want a separate ranking for each group.
For example, suppose we want to rank products within their category.
Run:
123456789101112SELECT id, name, category, price, ROW_NUMBER() OVER ( PARTITION BY category ORDER BY price DESC ) AS category_positionFROM productsWHERE id <= 10ORDER BY category, category_position;You should see:
123456789101112 id | name | category | price | category_position----+------------+----------+-------+------------------- 10 | Product 10 | audio | 5.10 | 1 5 | Product 5 | audio | 5.05 | 2 9 | Product 9 | garden | 5.09 | 1 4 | Product 4 | garden | 5.04 | 2 6 | Product 6 | home | 5.06 | 1 1 | Product 1 | home | 5.01 | 2 8 | Product 8 | office | 5.08 | 1 3 | Product 3 | office | 5.03 | 2 7 | Product 7 | outdoor | 5.07 | 1 2 | Product 2 | outdoor | 5.02 | 2Now the window contains both:
1PARTITION BY categoryand:
1ORDER BY price DESCPostgreSQL first divides the rows by category.
Then it orders the rows by price inside each category.
Finally, ROW_NUMBER() assigns numbers starting again from 1 in each partition.
Conceptually:
12345678910111213141516audioProduct 10 5.10 → 1Product 5 5.05 → 2gardenProduct 9 5.09 → 1Product 4 5.04 → 2homeProduct 6 5.06 → 1Product 1 5.01 → 2123456789101112131415161718192021222324all product rows │ ▼PARTITION BY category │ ├── audio │ ├── 1 │ └── 2 │ ├── garden │ ├── 1 │ └── 2 │ ├── home │ ├── 1 │ └── 2 │ ├── office │ ├── 1 │ └── 2 │ └── outdoor ├── 1 └── 2The numbering restarts for every partition.
What happens when values tie?
ROW_NUMBER() always assigns different numbers to different rows.
But sometimes multiple rows have the same value used for ranking.
To see how PostgreSQL handles ties, use this small dataset:
12345678910111213141516171819SELECT employee, salary, ROW_NUMBER() OVER ( ORDER BY salary DESC ) AS row_number, RANK() OVER ( ORDER BY salary DESC ) AS rank, DENSE_RANK() OVER ( ORDER BY salary DESC ) AS dense_rankFROM ( VALUES ('Alice', 120000), ('Charlie', 120000), ('Bob', 110000), ('David', 100000)) AS employees(employee, salary);The result demonstrates the difference:
123456 employee | salary | row_number | rank | dense_rank----------+--------+------------+------+------------ Alice | 120000 | 1 | 1 | 1 Charlie | 120000 | 2 | 1 | 1 Bob | 110000 | 3 | 3 | 2 David | 100000 | 4 | 4 | 3Alice and Charlie have the same salary.
That lets us see how each function behaves.
ROW_NUMBER gives every row its own position
ROW_NUMBER() gives every row a different sequential number:
123456salary row_number120000 1120000 2110000 3100000 4Even when two rows have the same salary, they receive different row numbers.
So ROW_NUMBER() answers:
What is the position of this individual row in the ordered sequence?
RANK leaves gaps after a tie
RANK() gives tied rows the same rank:
123456salary rank120000 1120000 1110000 3100000 4Notice what happens after the tie.
Two rows occupy rank 1, so the next rank is 3.
RANK() therefore leaves gaps after ties.
DENSE_RANK leaves no gaps
DENSE_RANK() also gives tied rows the same rank:
123456salary dense_rank120000 1120000 1110000 2100000 3But unlike RANK(), it doesn't leave gaps.
The next distinct salary receives the next rank.
Comparing the three
A useful summary is:
123456values ROW_NUMBER RANK DENSE_RANK120000 1 1 1120000 2 1 1110000 3 3 2100000 4 4 3Use ROW_NUMBER() when every row needs its own position.
Use RANK() when tied values should share a rank and the gap after the tie matters.
Use DENSE_RANK() when tied values should share a rank but you don't want gaps.
Ties and ROW_NUMBER
There is another important detail.
Consider:
123ROW_NUMBER() OVER ( ORDER BY salary DESC)If two rows have the same salary, the salary alone does not determine which tied row receives the smaller row number.
If you need deterministic ordering, add another expression:
123ROW_NUMBER() OVER ( ORDER BY salary DESC, employee)Now the window has a clear ordering even when salaries are equal.
The same principle applies to real application queries.
For example:
1234ROW_NUMBER() OVER ( PARTITION BY customer_id ORDER BY placed_at DESC, id DESC)uses id as a tie-breaker if two orders have the same placed_at.
A common use case: latest row per group
Imagine we want to number each customer's orders from newest to oldest:
123456789SELECT id, customer_id, placed_at, ROW_NUMBER() OVER ( PARTITION BY customer_id ORDER BY placed_at DESC, id DESC ) AS order_numberFROM orders;For every customer:
1order_number = 1represents their newest order.
This pattern is extremely common.
It can be used for:
1234latest order per customerlatest login per userhighest-priced product per categorymost recent event per deviceFiltering by a window result
Suppose we now want only the newest order for each customer.
We cannot put the window function directly in WHERE because the window result is calculated after WHERE.
Instead, calculate the row number first:
12345678910111213SELECT *FROM ( SELECT id, customer_id, placed_at, ROW_NUMBER() OVER ( PARTITION BY customer_id ORDER BY placed_at DESC, id DESC ) AS order_number FROM orders) ranked_ordersWHERE order_number = 1;The inner query assigns the row numbers.
The outer query can then filter them.
Conceptually:
12345678910111213orders │ ▼ROW_NUMBER() │ ▼ranked result │ ▼WHERE order_number = 1 │ ▼latest order per customerThis is one of the most useful window-function patterns to recognize in interviews.
Other ranking functions
PostgreSQL also provides ranking-related functions such as:
123PERCENT_RANK()CUME_DIST()NTILE()These are useful for percentile and distribution-style analysis.
For most application work, however, the ranking functions you are most likely to use are:
123ROW_NUMBER()RANK()DENSE_RANK()The important point is:
Ranking functions assign positions to rows while allowing the original rows to remain in the result, and `PARTITION BY` lets that ranking restart independently for each group.