Top-N per group query in SQL - Deep Dive

Overview - Top-N per group query

What is it?

A Top-N per group query finds the top N rows within each group of data in a table. For example, it can find the top 3 highest scores for each class in a school. It organizes data by groups and then selects only the best N entries from each group. This helps analyze data in smaller, meaningful chunks rather than the whole table at once.

Why it matters

Without Top-N per group queries, you would have to manually filter or write complex code to get the best entries per group. This makes it hard to compare or summarize data efficiently. Using this query saves time and helps businesses or analysts quickly find key insights like top salespeople per region or best products per category.

Where it fits

Before learning this, you should understand basic SQL SELECT queries, GROUP BY clauses, and window functions like ROW_NUMBER(). After mastering Top-N per group queries, you can explore advanced analytics like ranking, percentiles, and complex reporting queries.

Mental Model

Core Idea

Top-N per group queries rank rows within each group and pick only the top N based on that ranking.

Think of it like...

Imagine a school sports day where each class lines up and the teacher picks the top 3 fastest runners from each class. The teacher first sorts runners by speed within each class, then selects the top 3 from each line.

Table: Students
┌─────────┬───────────┬───────┐
│ Student │ Class     │ Score │
├─────────┼───────────┼───────┤
│ Alice   │ 1         │ 95    │
│ Bob     │ 1         │ 89    │
│ Carol   │ 2         │ 92    │
│ Dave    │ 2         │ 88    │
└─────────┴───────────┴───────┘

Process:
Group by Class → Rank by Score → Select top N per Class

Build-Up - 6 Steps

1

Foundation

Understanding grouping in SQL

Concept: Grouping data by a column to organize rows into sets.

In SQL, GROUP BY lets you collect rows that share the same value in one or more columns. For example, grouping sales by region shows total sales per region. This is the first step to handle data in groups.

Result

Rows are divided into groups based on the grouping column(s).

Understanding grouping is essential because Top-N per group queries operate within these groups to find the best rows.

2

Foundation

Ranking rows within groups

Concept: Assigning a rank number to each row inside its group based on a sorting order.

SQL window functions like ROW_NUMBER() assign a unique rank to each row within a group. For example, ROW_NUMBER() OVER (PARTITION BY Class ORDER BY Score DESC) ranks students by score within each class.

Result

Each row gets a rank number starting at 1 for the highest score in its group.

Ranking rows lets you identify which rows are the top performers in each group.

3

Intermediate

Filtering top N rows per group

🤔Before reading on: do you think you can filter top N rows by using WHERE on ROW_NUMBER() directly? Commit to yes or no.

Concept: Using a subquery or CTE to filter rows where the rank is less than or equal to N.

You cannot filter on ROW_NUMBER() directly in the same query level because it's a window function. Instead, wrap the ranking query in a subquery or CTE, then filter on the rank column in the outer query to get top N rows per group.

Result

The query returns only the top N rows for each group based on the ranking.

Knowing how to filter after ranking is key to correctly extracting top N rows per group.

4

Intermediate

Using RANK() and DENSE_RANK() differences

🤔Do you think RANK() and DENSE_RANK() always produce the same results? Commit to yes or no.

Concept: Understanding how ties affect ranking functions and the resulting top N rows.

RANK() skips rank numbers after ties, causing gaps (e.g., 1,1,3). DENSE_RANK() does not skip numbers (e.g., 1,1,2). This affects which rows appear in top N if there are ties in scores.

Result

Different ranking functions can include more or fewer rows when ties occur.

Choosing the right ranking function matters when handling ties in top N queries.

5

Advanced

Optimizing Top-N per group queries

🤔Is it always efficient to rank all rows before filtering top N? Commit to yes or no.

Concept: Techniques to improve performance by limiting rows processed or using indexes.

Some databases support optimization like applying LIMIT inside window functions or using indexes on grouping and ordering columns. Also, filtering early or using lateral joins can reduce workload.

Result

Queries run faster and use fewer resources on large datasets.

Understanding optimization helps write scalable Top-N per group queries for real-world data.

6

Expert

Handling ties and pagination in Top-N per group

🤔Can you paginate top N per group results without losing group boundaries? Commit to yes or no.

Concept: Advanced handling of ties and paginating results while preserving group structure.

When ties cause more than N rows per group, you may want to include all tied rows or limit strictly. Pagination requires careful windowing and filtering to avoid mixing groups across pages. Techniques include using ROW_NUMBER() with OFFSET/FETCH or keyset pagination.

Result

Accurate, user-friendly top N per group results with consistent pagination.

Mastering these details prevents subtle bugs and improves user experience in applications.

Under the Hood

Top-N per group queries use window functions that compute rankings over partitions (groups) of data. The database engine processes the entire dataset, partitions rows by group keys, sorts each partition by the specified order, and assigns rank numbers. Then, filtering on these ranks extracts the top N rows per group. This happens during query execution planning and optimization phases.

Why designed this way?

Window functions were introduced to solve complex analytic queries without multiple joins or subqueries. They allow ranking and aggregation over partitions efficiently. Alternatives like correlated subqueries were slower and harder to write. The design balances expressiveness and performance.

Query Execution Flow
┌───────────────┐
│ Input Table   │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Partition by  │
│ Group Column  │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Sort by Order │
│ Column (DESC) │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Assign Rank   │
│ (ROW_NUMBER)  │
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Filter Rank ≤N│
└──────┬────────┘
       │
       ▼
┌───────────────┐
│ Output Result │
└───────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Does filtering WHERE ROW_NUMBER() = 1 work inside the same SELECT query? Commit yes or no.

Common Belief: You can filter rows by ROW_NUMBER() directly in the WHERE clause of the same query.

Reality: Window functions like ROW_NUMBER() are applied after WHERE filtering, so you must use a subquery or CTE to filter on them.

Why it matters: Trying to filter directly causes errors or empty results, confusing beginners and wasting time.

Quick: Does RANK() always return the same number of rows as ROW_NUMBER()? Commit yes or no.

Common Belief: RANK() and ROW_NUMBER() produce the same number of rows and behave identically.

Reality: RANK() assigns the same rank to ties and can skip ranks, while ROW_NUMBER() always assigns unique ranks. This affects which rows appear in top N.

Why it matters: Using the wrong ranking function can lead to unexpected missing or extra rows in results.

Quick: Is Top-N per group query always fast on large tables? Commit yes or no.

Common Belief: Top-N per group queries are always efficient regardless of table size or indexes.

Reality: Without proper indexes or query optimization, these queries can be slow because they process all rows before filtering.

Why it matters: Ignoring performance can cause slow reports and unhappy users in production.

Quick: Can you use LIMIT inside window functions to get top N per group? Commit yes or no.

Common Belief: You can put LIMIT inside window functions to limit rows per group directly.

Reality: SQL does not allow LIMIT inside window functions; filtering must happen outside after ranking.

Why it matters: Misunderstanding this leads to invalid syntax errors and confusion.

Expert Zone

1

Ranking functions are non-deterministic without a unique ORDER BY tie-breaker, causing inconsistent results across executions.

2

Some databases optimize Top-N per group queries differently; knowing your DBMS's execution plan helps write efficient queries.

3

Handling NULLs in ORDER BY affects ranking; explicit NULLS FIRST/LAST clauses can change which rows appear in top N.

When NOT to use

Avoid Top-N per group queries when you need approximate results or very large datasets where sampling or pre-aggregated summaries are better. Alternatives include using materialized views, summary tables, or specialized analytics platforms.

Production Patterns

Common patterns include dashboards showing top customers per region, leaderboards per game level, and sales reports with top products per category. Professionals often combine Top-N per group with pagination, caching, and incremental updates for performance.

Connections

Window functions

Top-N per group queries build directly on window functions like ROW_NUMBER() and RANK().

Understanding window functions deeply unlocks many advanced SQL analytics beyond just Top-N queries.

Data partitioning in distributed systems

Both group data into partitions to process subsets independently.

Knowing how data is partitioned in distributed databases helps optimize Top-N per group queries at scale.

Tournament ranking systems (sports)

Both rank competitors within groups to find winners or top performers.

Recognizing ranking logic in sports helps grasp SQL ranking functions and their tie-breaking rules.

Common Pitfalls

#1 Filtering on ROW_NUMBER() in the same query level.

Wrong approach:

SELECT Student, Class, Score, ROW_NUMBER() OVER (PARTITION BY Class ORDER BY Score DESC) AS rn FROM Students WHERE rn <= 3;

Correct approach:

WITH Ranked AS (SELECT Student, Class, Score, ROW_NUMBER() OVER (PARTITION BY Class ORDER BY Score DESC) AS rn FROM Students) SELECT Student, Class, Score FROM Ranked WHERE rn <= 3;

Root cause:

Window functions are computed after WHERE clause, so filtering on them requires a subquery or CTE.

#2 Using RANK() without considering ties in top N.

Wrong approach:

SELECT * FROM (SELECT *, RANK() OVER (PARTITION BY Class ORDER BY Score DESC) AS rnk FROM Students) WHERE rnk <= 3;

Correct approach:

Use DENSE_RANK() if you want consecutive ranks without gaps, or handle ties explicitly depending on requirements.

Root cause:

Misunderstanding how RANK() handles ties leads to unexpected number of rows.

#3 Not indexing columns used in PARTITION BY and ORDER BY.

Wrong approach:

Running Top-N per group queries on large tables without indexes on Class and Score columns.

Correct approach:

Create indexes on (Class, Score DESC) to speed up partitioning and sorting.

Root cause:

Ignoring indexing causes full table scans and slow query performance.

Key Takeaways

Top-N per group queries let you find the best N rows within each group by ranking rows using window functions.

You must use a subquery or CTE to filter on ranking results because window functions are computed after WHERE clauses.

Choosing the right ranking function (ROW_NUMBER, RANK, DENSE_RANK) affects how ties are handled and which rows appear in the top N.

Performance depends on proper indexing and query optimization, especially on large datasets.

Advanced use includes handling ties carefully and paginating results while preserving group boundaries.

Practice

1. What does the ROW_NUMBER() OVER (PARTITION BY column ORDER BY column) clause do in a SQL query?

easy

A. Assigns a unique rank number to each row within each group defined by the partition column.

B. Counts the total number of rows in the entire table.

C. Filters rows to only include those with the highest value in the column.

D. Joins two tables based on the partition column.

Solution

1. Step 1: Understand ROW_NUMBER() function

   ROW_NUMBER() assigns a unique sequential number to rows in the result set.

2. Step 2: Understand PARTITION BY usage

   PARTITION BY divides rows into groups, and ROW_NUMBER() restarts numbering for each group.

3. Final Answer:

   Assigns a unique rank number to each row within each group defined by the partition column. -> Option A

4. Quick Check:

   ROW_NUMBER() with PARTITION BY = unique rank per group [OK]

Hint: ROW_NUMBER() with PARTITION BY ranks rows per group [OK]

Common Mistakes:

• Thinking ROW_NUMBER() counts total rows
• Confusing PARTITION BY with filtering
• Assuming it filters rows automatically

2. Which of the following SQL queries correctly selects the top 2 highest salaries per department from an employees table?

easy

A. SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn FROM employees) sub WHERE rn <= 2;

B. SELECT * FROM employees ORDER BY salary DESC LIMIT 2 PARTITION BY department;

C. SELECT * FROM employees WHERE ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) <= 2;

D. SELECT TOP 2 * FROM employees PARTITION BY department ORDER BY salary DESC;

Solution

1. Step 1: Understand ROW_NUMBER() usage in subquery

   ROW_NUMBER() must be used in a subquery or CTE to assign ranks before filtering.

2. Step 2: Filter rows by rank in outer query

   The outer query filters rows where the rank (rn) is 2 or less per department.

3. Final Answer:

   SELECT * FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn FROM employees) sub WHERE rn <= 2; -> Option A

4. Quick Check:

   Use subquery with ROW_NUMBER() and filter by rank [OK]

Hint: Use subquery with ROW_NUMBER() then filter by rank [OK]

Common Mistakes:

• Trying to filter ROW_NUMBER() directly in WHERE clause
• Using LIMIT with PARTITION BY (not valid syntax)
• Using TOP without proper partitioning

3. Given the table sales with columns region, salesperson, and amount, what will this query return?

SELECT region, salesperson, amount FROM (SELECT region, salesperson, amount, ROW_NUMBER() OVER (PARTITION BY region ORDER BY amount DESC) AS rn FROM sales) AS ranked WHERE rn = 1;

medium

A. All sales records sorted by amount descending.

B. All sales records where amount equals 1.

C. The highest sale amount record for each region.

D. The lowest sale amount record for each region.

Solution

1. Step 1: Analyze ROW_NUMBER() with PARTITION BY region

   Rows are grouped by region and ranked by amount descending, so rn=1 is highest amount per region.

2. Step 2: Filter WHERE rn = 1

   This keeps only the top-ranked row per region, i.e., the highest sale amount record.

3. Final Answer:

   The highest sale amount record for each region. -> Option C

4. Quick Check:

   ROW_NUMBER() = 1 per group returns top record [OK]

Hint: Filter WHERE rn=1 to get top record per group [OK]

Common Mistakes:

• Thinking it returns all rows sorted
• Confusing rn=1 with lowest amount
• Assuming amount = 1 filters rows

4. Identify the error in this SQL query that tries to get the top 3 recent orders per customer from orders table:

SELECT customer_id, order_id, order_date, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rank FROM orders WHERE rank <= 3;

medium

A. Missing GROUP BY clause for customer_id.

B. ROW_NUMBER() cannot be used with PARTITION BY.

C. ORDER BY must be ascending, not descending.

D. You cannot filter on an alias in the WHERE clause directly.

Solution

1. Step 1: Understand alias usage in WHERE clause

   Aliases like 'rank' defined in SELECT cannot be used in WHERE clause directly because WHERE is evaluated before SELECT.

2. Step 2: Correct approach using subquery or CTE

   Use a subquery or CTE to assign ROW_NUMBER(), then filter on 'rank' in outer query.

3. Final Answer:

   You cannot filter on an alias in the WHERE clause directly. -> Option D

4. Quick Check:

   Filter on ROW_NUMBER() alias in outer query, not WHERE [OK]

Hint: Filter ROW_NUMBER() in outer query, not WHERE clause [OK]

Common Mistakes:

• Using alias in WHERE clause directly
• Thinking ORDER BY DESC is invalid
• Adding unnecessary GROUP BY

5. You have a products table with columns category, product_id, and price. You want to find the top 2 most expensive products per category, but if two products have the same price, include both even if it means more than 2 per category. Which query achieves this?

hard

A. Use ROW_NUMBER() partitioned by category ordered by price DESC and filter WHERE rn <= 2.

B. Use RANK() partitioned by category ordered by price DESC and filter WHERE rank <= 2.

C. Use DENSE_RANK() partitioned by category ordered by price ASC and filter WHERE dense_rank <= 2.

D. Use COUNT() partitioned by category ordered by price DESC and filter WHERE count <= 2.

Solution

1. Step 1: Understand difference between ROW_NUMBER(), RANK(), and DENSE_RANK()

   ROW_NUMBER() assigns unique ranks, so ties get different numbers. RANK() assigns same rank to ties and skips ranks after. DENSE_RANK() assigns same rank to ties but no gaps.

2. Step 2: Choose RANK() to include ties at second place

   RANK() allows including all products tied at rank 2, possibly more than 2 rows per category.

3. Final Answer:

   Use RANK() partitioned by category ordered by price DESC and filter WHERE rank <= 2. -> Option B

4. Quick Check:

   RANK() includes ties with same rank [OK]

Hint: Use RANK() to include ties in top N per group [OK]

Common Mistakes:

• Using ROW_NUMBER() excludes ties
• Using DENSE_RANK() includes ties but no gaps
• Using COUNT() is invalid for ranking