SQL RANK OVER PARTITION BY: Top N Per Group
· 6 min read
RANK() OVER (PARTITION BY category ORDER BY revenue DESC) numbers the rows inside each category from highest revenue to lowest, starting again at 1 for every category. PARTITION BY says where the numbering restarts. ORDER BY inside the brackets says what decides first place. That is what rank over partition by means in plain words.
SELECT category, product, revenue,
RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS rnk
FROM product_sales
ORDER BY category, rnk, product;Unlike GROUP BY, a window function does not collapse rows: every product stays in the result and simply gains a rank column. Below: the output, rank vs dense_rank vs row_number when there are ties, and how to keep the top N per group.
A worked example
| category | product | revenue |
|---|---|---|
| Office | Copy paper | 900 |
| Office | Toner | 900 |
| Office | Stapler | 300 |
| Office | Pens | 200 |
| Kitchen | Coffee beans | 1200 |
| Kitchen | Paper cups | 400 |
| Kitchen | Tea | 250 |
| Cleaning | Floor cleaner | 500 |
| Cleaning | Mop | 150 |
The query above returns:
| category | product | revenue | rnk |
|---|---|---|---|
| Cleaning | Floor cleaner | 500 | 1 |
| Cleaning | Mop | 150 | 2 |
| Kitchen | Coffee beans | 1200 | 1 |
| Kitchen | Paper cups | 400 | 2 |
| Kitchen | Tea | 250 | 3 |
| Office | Copy paper | 900 | 1 |
| Office | Toner | 900 | 1 |
| Office | Stapler | 300 | 3 |
| Office | Pens | 200 | 4 |
Each category has its own ranking. Copy paper and Toner tie at 900, so both are ranked 1, and the next Office product is ranked 3, not 2. That gap is what makes RANK different from its siblings.
The pieces of a SQL window function
RANK(): the window function.OVER (...): marks it as a window function and defines the window.PARTITION BY category: split the rows into groups; ranking restarts in each. Leave it out and the whole table is one group.ORDER BY revenue DESC: the sort that decides the rank.DESCmeans the biggest value gets 1.
You can partition by more than one column, such as PARTITION BY region, category.
Rank vs dense_rank vs row_number
All three number rows in order. They only differ when there are ties. Here are the Office products with all three side by side:
SELECT product, revenue,
ROW_NUMBER() OVER (ORDER BY revenue DESC, product) AS row_num,
RANK() OVER (ORDER BY revenue DESC) AS rnk,
DENSE_RANK() OVER (ORDER BY revenue DESC) AS dense_rnk
FROM product_sales
WHERE category = 'Office'
ORDER BY revenue DESC, product;| product | revenue | row_num | rnk | dense_rnk |
|---|---|---|---|---|
| Copy paper | 900 | 1 | 1 | 1 |
| Toner | 900 | 2 | 1 | 1 |
| Stapler | 300 | 3 | 3 | 2 |
| Pens | 200 | 4 | 4 | 3 |
- ROW_NUMBER gives every row a different number, even on a tie. Who gets 1 and who gets 2 is decided by the extra
productin theORDER BY. Without a tie-breaker, the database picks, and it may pick differently next time. - RANK gives ties the same number, then skips: 1, 1, 3. Like a race where two runners share gold and nobody gets silver.
- DENSE_RANK gives ties the same number and does not skip: 1, 1, 2.
Which one to use depends on the question. "The best seller in each category" with ties allowed: RANK. "Exactly one row per category": ROW_NUMBER with a tie-breaker. "The top 3 distinct revenue levels": DENSE_RANK.
Top N per group
You cannot filter on a window function in WHERE:
SELECT category, product, revenue
FROM product_sales
WHERE RANK() OVER (PARTITION BY category ORDER BY revenue DESC) = 1;DuckDB replies WHERE clause cannot contain window functions!, and every other database refuses too. Window functions are calculated after WHERE, so the rank does not exist yet. Calculate the rank in a subquery (or a CTE) and filter outside it:
SELECT category, product, revenue
FROM (
SELECT category, product, revenue,
RANK() OVER (PARTITION BY category ORDER BY revenue DESC) AS rnk
FROM product_sales
) AS ranked
WHERE rnk <= 2
ORDER BY category, revenue DESC, product;| category | product | revenue |
|---|---|---|
| Cleaning | Floor cleaner | 500 |
| Cleaning | Mop | 150 |
| Kitchen | Coffee beans | 1200 |
| Kitchen | Paper cups | 400 |
| Office | Copy paper | 900 |
| Office | Toner | 900 |
Office's top 2 are its two tied products. Change rnk <= 2 to rnk = 1 to get the top product per category: you would get four rows, because Office has two winners.
QUALIFY: the shortcut in some databases
DuckDB, Snowflake, BigQuery and Databricks support QUALIFY, a WHERE for window functions:
SELECT category, product, revenue
FROM product_sales
QUALIFY ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC, product) = 1
ORDER BY category;| category | product | revenue |
|---|---|---|
| Cleaning | Floor cleaner | 500 |
| Kitchen | Coffee beans | 1200 |
| Office | Copy paper | 900 |
Postgres, MySQL and SQL Server do not have QUALIFY, so use the subquery there.
Top N overall vs top N per group
If you want the top 5 products across the whole table, you do not need a window function at all: ORDER BY revenue DESC LIMIT 5 does it (SELECT TOP 5 in SQL Server). Reach for PARTITION BY only when the question says "in each" or "per".
Common mistakes
Forgetting PARTITION BY (the AI mistake to watch for)
Ask an AI assistant for "the best-selling product in each category" and a common slip is a window with an ORDER BY but no PARTITION BY:
SELECT category, product, revenue
FROM (
SELECT category, product, revenue,
RANK() OVER (ORDER BY revenue DESC) AS rnk
FROM product_sales
) AS ranked
WHERE rnk = 1;| category | product | revenue |
|---|---|---|
| Kitchen | Coffee beans | 1200 |
One row. Without PARTITION BY, the ranking runs across the whole table, so only the overall winner gets rank 1. The assistant may still describe the result as "the top product per category".
How to spot it: count the rows. "Top 1 per category" should return at least one row per category. If you have three categories and get one row back, the partition is missing.
Using ROW_NUMBER when ties matter
Swap RANK for ROW_NUMBER in the top-1 query and Office returns either Copy paper or Toner, not both. That might be fine, but it should be a choice. If the business question is "who is the best seller", hiding a tie is misleading.
Ranking in the wrong direction
ORDER BY revenue without DESC ranks the lowest revenue as 1. Glance at the rank-1 rows: they should be the biggest numbers.
FAQ
What is the difference between PARTITION BY and GROUP BY?
GROUP BY collapses rows into one per group. PARTITION BY keeps every row and only tells the window function where groups begin and end. You can use both in one query.
Can I use RANK without PARTITION BY?
Yes. Then the whole result is ranked as one group, which is what you want for an overall leaderboard.
How do I get the second highest value per group?
Use DENSE_RANK() OVER (PARTITION BY ... ORDER BY value DESC) in a subquery and keep = 2. DENSE_RANK makes sure a tie at the top does not push the second value to rank 3.
Which databases support RANK and DENSE_RANK?
All the common ones: Postgres, MySQL 8 and later, SQL Server, Oracle, SQLite 3.25 and later, DuckDB, BigQuery and Snowflake.
Window functions also power the SQL running total. If you are preparing for interviews, ranking questions are a favourite: see SQL interview questions for data analysts.
Practise this on a realistic dataset
Solve it yourself on one company's data, with instant feedback that points at what's wrong without giving away the answer.
Try “Best seller in each category” →