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.