Finding the top N rows per group is one of the most common analytical SQL challenges — and one of the most misunderstood. This lesson teaches you to solve it using correlated subqueries and derived tables, with real business scenarios, tie-handling strategies, and production-ready patterns that work across any SQL dialect.

Imagine you're a data analyst at a retail company. Your VP of Sales just asked you: "Give me the top 3 best-selling products in each category for last quarter." You know how to find the top 3 products overall — that's just ORDER BY revenue DESC LIMIT 3. But per category? That's a different animal. You have to rank products within each category, then filter to only the top-ranked ones. It's one of the most common analytical queries in production SQL, and it trips up even experienced practitioners who haven't seen the pattern before.
This lesson teaches you to solve that problem using the tools you already know: subqueries and derived tables. You'll learn a technique that works across virtually every SQL dialect, including MySQL 5.x, SQLite, older versions of SQL Server, and any environment where window functions either aren't available or you're not yet comfortable reaching for them. By understanding why this approach works — not just copying the pattern — you'll be able to adapt it to any "top N per group" question that lands in your inbox.
By the end of this lesson, you'll be writing confident, correct queries that answer ranked-within-group questions, and you'll understand the tradeoffs clearly enough to explain your approach to a teammate.
What you'll learn:
You should be comfortable writing SELECT statements with WHERE, ORDER BY, and GROUP BY. You should also have a working understanding of subqueries — specifically, what it means for a subquery to reference a column from an outer query (a correlated subquery). If you need a refresher on that last point, Understanding SQL Subqueries: Filtering and Looking Up Data with Nested SELECT Statements covers the foundation clearly.
Before writing a single line of SQL, let's get the mental model right. When you say "top 3 products per category," what does top 3 actually mean? It means: for a given product, there are fewer than 3 other products in the same category with a higher revenue. That's the key translation.
If only 0, 1, or 2 other products in the same category beat product X, then product X is in the top 3.
Let's make that concrete. Say you have these products in the "Electronics" category:
| product_id | product_name | category | revenue |
|---|---|---|---|
| 101 | Wireless Headphones | Electronics | 85000 |
| 102 | Smart Speaker | Electronics | 62000 |
| 103 | Bluetooth Keyboard | Electronics | 47000 |
| 104 | USB-C Hub | Electronics | 31000 |
| 105 | Screen Protector | Electronics | 18000 |
For "Wireless Headphones" (revenue $85,000): how many Electronics products have higher revenue? Zero. So it's rank 1.
For "Bluetooth Keyboard" (revenue $47,000): how many have higher revenue? Two (Headphones and Smart Speaker). So it's rank 3.
For "USB-C Hub" (revenue $31,000): three products have higher revenue. So it's rank 4 — out of the top 3.
That counting logic maps directly to SQL. And that's the engine behind the correlated subquery approach.
Key insight
"Top N" is equivalent to "count of rows that beat this row is less than N." Once you see that translation, the query structure falls into place naturally.
Let's work with a realistic dataset you'd actually encounter. We'll use a product sales scenario with three tables.
-- Products table
CREATE TABLE products (
product_id INT PRIMARY KEY,
product_name VARCHAR(100),
category VARCHAR(50)
);
-- Sales table (quarterly summary)
CREATE TABLE sales_summary (
product_id INT,
quarter VARCHAR(10),
total_revenue DECIMAL(12, 2),
units_sold INT
);
And here's representative data across three categories:
INSERT INTO products VALUES
(101, 'Wireless Headphones', 'Electronics'),
(102, 'Smart Speaker', 'Electronics'),
(103, 'Bluetooth Keyboard', 'Electronics'),
(104, 'USB-C Hub', 'Electronics'),
(105, 'Screen Protector', 'Electronics'),
(201, 'Running Shoes', 'Footwear'),
(202, 'Hiking Boots', 'Footwear'),
(203, 'Sandals', 'Footwear'),
(204, 'Dress Shoes', 'Footwear'),
(301, 'Yoga Mat', 'Fitness'),
(302, 'Resistance Bands', 'Fitness'),
(303, 'Foam Roller', 'Fitness'),
(304, 'Jump Rope', 'Fitness');
INSERT INTO sales_summary VALUES
(101, 'Q4-2024', 85000.00, 1200),
(102, 'Q4-2024', 62000.00, 890),
(103, 'Q4-2024', 47000.00, 640),
(104, 'Q4-2024', 31000.00, 520),
(105, 'Q4-2024', 18000.00, 980),
(201, 'Q4-2024', 71000.00, 430),
(202, 'Q4-2024', 58000.00, 310),
(203, 'Q4-2024', 44000.00, 600),
(204, 'Q4-2024', 39000.00, 220),
(301, 'Q4-2024', 29000.00, 750),
(302, 'Q4-2024', 52000.00, 960),
(303, 'Q4-2024', 38000.00, 510),
(304, 'Q4-2024', 21000.00, 430);
The goal: find the top 3 products by revenue in each category for Q4-2024.
The first building block is a subquery that, for any given row, counts how many other rows in the same group have a higher value. Let's start by pulling together the data we need in a single query before ranking:
SELECT
p.category,
p.product_name,
s.total_revenue
FROM products p
JOIN sales_summary s
ON p.product_id = s.product_id
WHERE s.quarter = 'Q4-2024'
ORDER BY p.category, s.total_revenue DESC;
This gives you a clean flat view of all products with their revenues, sorted within each category. If you need a refresher on how this JOIN works, SQL JOINs Explained with Real-World Examples has you covered.
Now, the correlated subquery. For each row in the outer query, we want to count how many other rows share the same category and have strictly higher revenue:
SELECT
p.category,
p.product_name,
s.total_revenue,
(
SELECT COUNT(*)
FROM products p2
JOIN sales_summary s2
ON p2.product_id = s2.product_id
WHERE p2.category = p.category -- same category as outer row
AND s2.quarter = s.quarter -- same quarter
AND s2.total_revenue > s.total_revenue -- strictly higher revenue
) AS competitors_above
FROM products p
JOIN sales_summary s
ON p.product_id = s.product_id
WHERE s.quarter = 'Q4-2024'
ORDER BY p.category, s.total_revenue DESC;
Notice the references to p.category, s.quarter, and s.total_revenue inside the subquery — those are pulling from the outer query. That's what makes this a correlated subquery. For each row the outer query processes, SQL re-runs the inner query with those specific values plugged in.
The result looks like this:
| category | product_name | total_revenue | competitors_above |
|---|---|---|---|
| Electronics | Wireless Headphones | 85000.00 | 0 |
| Electronics | Smart Speaker | 62000.00 | 1 |
| Electronics | Bluetooth Keyboard | 47000.00 | 2 |
| Electronics | USB-C Hub | 31000.00 | 3 |
| Electronics | Screen Protector | 18000.00 | 4 |
| Fitness | Resistance Bands | 52000.00 | 0 |
| Fitness | Foam Roller | 38000.00 | 1 |
| Fitness | Yoga Mat | 29000.00 | 2 |
| Fitness | Jump Rope | 21000.00 | 3 |
| Footwear | Running Shoes | 71000.00 | 0 |
| Footwear | Hiking Boots | 58000.00 | 1 |
| Footwear | Sandals | 44000.00 | 2 |
| Footwear | Dress Shoes | 39000.00 | 3 |
The competitors_above column is effectively a rank. Products with competitors_above < 3 are in the top 3. You can already see the pattern — now we just need to filter on it.
Note
The competitors_above count is equivalent to what window functions call RANK() - 1. When zero other rows beat you, you're rank 1. When two rows beat you, you're rank 3. This is dense-rank-style thinking only when there are no ties — we'll address ties shortly.
You can't put the correlated subquery directly in a WHERE clause as a filter because WHERE is evaluated before the SELECT column list is computed. That means you can't write WHERE competitors_above < 3 referencing a column alias from SELECT. The solution is to wrap the whole query as a derived table — an inline view — and filter on the outer query.
If you've worked through Writing SQL FROM Scratch: Structuring Multi-Step Analytical Queries with Derived Tables and Inline Views, this pattern will feel familiar. A derived table is just a subquery in the FROM clause that you treat like a regular table.
Here's the complete query:
SELECT
category,
product_name,
total_revenue
FROM (
SELECT
p.category,
p.product_name,
s.total_revenue,
(
SELECT COUNT(*)
FROM products p2
JOIN sales_summary s2
ON p2.product_id = s2.product_id
WHERE p2.category = p.category
AND s2.quarter = s.quarter
AND s2.total_revenue > s.total_revenue
) AS competitors_above
FROM products p
JOIN sales_summary s
ON p.product_id = s.product_id
WHERE s.quarter = 'Q4-2024'
) AS ranked_products
WHERE competitors_above < 3
ORDER BY category, total_revenue DESC;
Result:
| category | product_name | total_revenue |
|---|---|---|
| Electronics | Wireless Headphones | 85000.00 |
| Electronics | Smart Speaker | 62000.00 |
| Electronics | Bluetooth Keyboard | 47000.00 |
| Fitness | Resistance Bands | 52000.00 |
| Fitness | Foam Roller | 38000.00 |
| Fitness | Yoga Mat | 29000.00 |
| Footwear | Running Shoes | 71000.00 |
| Footwear | Hiking Boots | 58000.00 |
| Footwear | Sandals | 44000.00 |
That's exactly what the VP asked for: the top 3 products by revenue in each category.
Tip
Always give your derived table an alias — AS ranked_products above. Most SQL databases require it, and it makes your query dramatically easier to read when someone else (or future you) has to maintain it. See Using SQL Aliases Effectively: Naming Columns and Tables for Readable, Maintainable Queries for conventions that hold up at scale.
Let's slow down and make sure the mechanics are clear, because this is the part where most people's mental model breaks down.
Why can't we filter inside the inner query? The correlated subquery computes a value for each row as the outer SELECT runs. At the point WHERE evaluates in the outer query, that value doesn't exist yet — WHERE runs before SELECT. So WHERE competitors_above < 3 would fail with a "column not found" error.
Why does the derived table fix this? When you wrap the entire query as a derived table, the database materializes (or at least logically resolves) the entire inner result — including competitors_above — before the outer WHERE clause evaluates. The outer query treats the derived table like a physical table, so all columns are available for filtering.
This is a fundamental pattern worth internalizing:
You'll use this two-layer structure constantly in analytical SQL. The same logic applies when you're building a complete analytical query from scratch — complex questions almost always decompose into an inner computation layer and an outer filtering layer.
The query above uses > s.total_revenue (strict greater-than). That's a deliberate choice, and it has specific behavior with ties. Let's say two products in the Electronics category both had $62,000 in revenue. What happens?
Using strict greater-than:
competitors_above count (1, since only the $85,000 product beats them)This matches what database engineers call RANK() behavior: tied rows get the same rank, and the rank after a tie skips numbers. If you want ROW_NUMBER() behavior — exactly N rows per group, even when there are ties, with ties broken arbitrarily — you need to add a tiebreaker to the comparison.
Option 1: Add a secondary sort to break ties deterministically
If you have a stable secondary criterion (say, product_id as a tiebreaker), include it in the correlated subquery's comparison:
(
SELECT COUNT(*)
FROM products p2
JOIN sales_summary s2
ON p2.product_id = s2.product_id
WHERE p2.category = p.category
AND s2.quarter = s.quarter
AND (
s2.total_revenue > s.total_revenue
OR (
s2.total_revenue = s.total_revenue
AND p2.product_id < p.product_id
)
)
) AS competitors_above
Now, among tied products, the one with the lower product_id ranks higher. This guarantees you get exactly N rows per group, with a consistent tiebreaker.
Option 2: Accept ties (RANK behavior) with a cap
If your business requirement is "show up to 3 but show all ties at the boundary," use the original > comparison and document the behavior. This is often the correct answer — if two products tied for 3rd place, excluding one arbitrarily would be misleading.
Warning
Always clarify the tie-handling requirement before you write the query. "Top 3" sounds obvious until two products are tied for 3rd place. Ask: do we want exactly 3 rows, or all rows that are legitimately in the top 3? The answer changes your comparison logic.
Real queries rarely stop at a single table. Let's extend the example. Suppose the VP adds a constraint: "Only include products that had at least 500 units sold." And they want the result to include the units sold in the output.
This is where the derived table structure really shows its value. You handle the filtering in the inner query, and the ranking logic stays clean:
SELECT
category,
product_name,
total_revenue,
units_sold
FROM (
SELECT
p.category,
p.product_name,
s.total_revenue,
s.units_sold,
(
SELECT COUNT(*)
FROM products p2
JOIN sales_summary s2
ON p2.product_id = s2.product_id
WHERE p2.category = p.category
AND s2.quarter = s.quarter
AND s2.total_revenue > s.total_revenue
AND s2.units_sold >= 500 -- tiebreaker: also filter competitors
) AS competitors_above
FROM products p
JOIN sales_summary s
ON p.product_id = s.product_id
WHERE s.quarter = 'Q4-2024'
AND s.units_sold >= 500 -- filter the outer rows too
) AS ranked_products
WHERE competitors_above < 3
ORDER BY category, total_revenue DESC;
Warning
Notice that the units_sold >= 500 filter appears in both the outer query's WHERE clause and inside the correlated subquery. This is essential. If you filter the outer rows but not the competitors inside the subquery, you'll count disqualified products as "competitors above," which inflates the rank and incorrectly excludes qualifying products. The inner and outer filters must match.
This double-filter requirement is a common source of subtle bugs. If you're ever getting fewer results than expected, check whether your correlated subquery's filtering conditions match the outer query's filtering conditions.
There's another way to implement the same logic without a correlated subquery — using a self-join. Some practitioners find this easier to reason about, and it can perform better in certain query planners.
The idea: join each product row against all products in the same category that beat it, then count those beating rows and filter.
SELECT
p.category,
p.product_name,
s.total_revenue
FROM products p
JOIN sales_summary s
ON p.product_id = s.product_id
LEFT JOIN (
products p2
JOIN sales_summary s2
ON p2.product_id = s2.product_id
) ON p2.category = p.category
AND s2.quarter = s.quarter
AND s2.total_revenue > s.total_revenue
WHERE s.quarter = 'Q4-2024'
GROUP BY
p.category,
p.product_name,
s.total_revenue
HAVING COUNT(p2.product_id) < 3
ORDER BY p.category, s.total_revenue DESC;
Here we LEFT JOIN each product against all higher-revenue products in the same category. For top-ranked products, there are zero matches — that's fine because it's a LEFT JOIN, so the row is preserved. We then GROUP BY the outer product and count how many competitors showed up. The HAVING COUNT(p2.product_id) < 3 is equivalent to our competitors_above < 3 filter.
If you've worked with filtering groups after aggregation, the HAVING clause here will feel natural — we're using it to filter based on the count of joined competitor rows.
Tip
The self-join approach and the correlated subquery approach are logically equivalent but can have different performance characteristics. The self-join version may work better in databases that optimize GROUP BY more aggressively than correlated subqueries. The correlated subquery version is often more readable and easier to modify. Try both on your dataset if performance matters.
A polished, production-ready version of this query makes N obvious and easy to change. Rather than burying the number 3 inside the WHERE clause, structure your query so N is clearly visible:
-- Set N = 3: change this one place to get top N results
SELECT
category,
product_name,
total_revenue,
competitors_above + 1 AS rank_in_category
FROM (
SELECT
p.category,
p.product_name,
s.total_revenue,
(
SELECT COUNT(*)
FROM products p2
JOIN sales_summary s2
ON p2.product_id = s2.product_id
WHERE p2.category = p.category
AND s2.quarter = s.quarter
AND s2.total_revenue > s.total_revenue
) AS competitors_above
FROM products p
JOIN sales_summary s
ON p.product_id = s.product_id
WHERE s.quarter = 'Q4-2024'
) AS ranked_products
WHERE competitors_above < 3 -- <-- change 3 to get top-N (N=3 means < 3)
ORDER BY category, competitors_above;
We've also added competitors_above + 1 AS rank_in_category to the output, which gives readers an explicit rank number (1, 2, 3) rather than the raw competitor count (0, 1, 2). This makes the output immediately interpretable in a report.
Let's be honest: correlated subqueries are not known for blazing performance. Because the inner subquery runs once for each row in the outer query, you can end up with a lot of repeated work on large tables. Here's what that looks like at scale:
For moderate-sized datasets (tens of thousands of rows), this is usually fine. For tables with millions of rows, you need to think carefully.
Practical mitigation strategies:
Index your grouping and sorting columns. An index on (category, total_revenue) in the joined result, or on (product_id, quarter, total_revenue) in sales_summary, lets the database satisfy each correlated subquery run with an index seek rather than a full scan. See SQL Indexes Explained: How They Work and When to Create Them for guidance on choosing the right index.
Filter aggressively before ranking. Apply your WHERE conditions (like the quarter filter) before the ranking computation runs, not after. The derived table structure naturally supports this — put your WHERE conditions in the inner query.
Consider window functions for large datasets. If your database supports them, ROW_NUMBER() OVER (PARTITION BY category ORDER BY total_revenue DESC) is almost always faster than the correlated subquery approach for large tables. This lesson teaches the subquery approach because it's portable and because understanding it deeply helps you understand what window functions are doing under the hood.
Note
The subquery approach taught here is intentionally compatible with environments that don't support window functions — MySQL 5.x, SQLite, older SQL Server, legacy reporting tools. If you're on a modern database (PostgreSQL, MySQL 8+, SQL Server 2012+, BigQuery, Snowflake), window functions are the idiomatic solution for this problem class. But even then, knowing this subquery pattern makes you a better SQL thinker.
Time to put this into practice. Use the schema and data from earlier in the lesson.
Exercise 1: Top 2 by Units Sold
Write a query that returns the top 2 products by units_sold in each category for Q4-2024. Output should include category, product_name, units_sold, and a rank_in_category column (1 or 2).
Exercise 2: Category Leaderboard with Multiple Metrics
Extend the top-3-by-revenue query to also include units_sold in the output. Sort the final results by category alphabetically and by total_revenue descending within each category.
Exercise 3: Add a New Quarter
Insert the following data for Q1-2025:
INSERT INTO sales_summary VALUES
(101, 'Q1-2025', 91000.00, 1350),
(102, 'Q1-2025', 55000.00, 780),
(103, 'Q1-2025', 68000.00, 830),
(104, 'Q1-2025', 25000.00, 410),
(105, 'Q1-2025', 22000.00, 1050),
(201, 'Q1-2025', 65000.00, 380),
(202, 'Q1-2025', 72000.00, 360),
(203, 'Q1-2025', 41000.00, 510),
(204, 'Q1-2025', 44000.00, 270),
(301, 'Q1-2025', 33000.00, 820),
(302, 'Q1-2025', 48000.00, 890),
(303, 'Q1-2025', 42000.00, 560),
(304, 'Q1-2025', 27000.00, 490);
Notice that in Electronics for Q1-2025, the Bluetooth Keyboard ($68,000) now outranks the Smart Speaker ($55,000). Write the top-3-by-revenue query for Q1-2025 and verify the Electronics ranking reflects this change.
Challenge Exercise: Cross-Quarter Comparison
Write a query that returns, for each category, the product that ranked #1 by revenue in Q4-2024, alongside that same product's revenue in Q1-2025. This will require your top-N pattern joined against another instance of sales_summary. Hint: start by isolating rank-1 products (competitors_above = 0) in a derived table, then join to Q1-2025 data.
Mistake 1: Forgetting to match the filter inside the correlated subquery
The most frequent bug. If your outer query filters by quarter but the correlated subquery doesn't, the subquery counts competitors across all quarters. Result: inflated competitor counts, too few rows returned.
Fix: Make sure every filter on the outer query that affects which rows compete with each other is also present inside the correlated subquery.
Mistake 2: Using >= instead of > in the correlated subquery
If you write s2.total_revenue >= s.total_revenue, you're counting the current row as a competitor against itself. Every product will count itself, adding 1 to all competitor counts. Your "top 3" filter (< 3) will then return 2 rows per group instead of 3.
Fix: Always use strict greater-than (>). You're counting rows other than the current row that beat it.
Mistake 3: Aliasing the derived table incorrectly or not at all
Some databases (MySQL in particular) require every derived table in the FROM clause to have an alias. Without one, you get a syntax error that looks confusing because the error points at the closing parenthesis.
Fix: Always alias your derived tables: FROM (...) AS ranked_products.
Mistake 4: Filtering on the alias inside the same SELECT level
Writing WHERE competitors_above < 3 in the same query that defines competitors_above in the SELECT list will fail with a "column not found" error (or silently produce wrong results in some databases). SQL evaluates WHERE before SELECT.
Fix: Wrap the query as a derived table and filter in the outer query. That's exactly what we did in Step 2.
Mistake 5: Expecting exactly N rows when ties exist
If using strict greater-than, tied rows at the boundary both get included. You might get 4 rows in a "top 3" group.
Fix: Add a tiebreaker to the correlated subquery comparison (using OR with a secondary column), or document that the query returns "top N plus any ties at the boundary" and confirm that's what the business wants.
Troubleshooting: Getting zero rows back
Check these in order:
competitors_above being computed correctly? Run the inner query alone (without the outer WHERE) and inspect the counts.<= 3 instead of < 3? Remember: 0, 1, 2 are the competitor counts for ranks 1, 2, 3.The debugging strategy is always to run the inner derived table query first, verify the competitors_above column looks right, and then apply the outer filter. For a systematic approach to tracing query bugs, Debugging SQL Queries: How to Read Error Messages, Trace Wrong Results, and Fix Broken Joins Step by Step walks through a proven methodology.
You now have a complete, portable technique for finding the top N rows per group using correlated subqueries and derived tables. Here's what to carry forward:
The pattern in one mental model: "Top N" = "fewer than N competitors with a higher value in the same group." Translate that into a correlated subquery that counts competitors, wrap it in a derived table, and filter on the count in the outer query.
The structure to remember:
SELECT [desired columns]
FROM (
SELECT [columns] + (correlated subquery) AS competitors_above
FROM [tables with joins and inner filters]
) AS ranked_result
WHERE competitors_above < N
ORDER BY [group column], [ranking column] DESC;
Key decisions you now know how to make:
> for RANK-style ties (tied rows both qualify)Where to go from here:
This lesson deliberately avoided window functions to give you a deep understanding of the underlying mechanics. Once you're solid here, the natural next step is to explore how ROW_NUMBER(), RANK(), and DENSE_RANK() express the same logic more concisely — and how to use them inside CTEs for even cleaner multi-step query structures. Advanced Subqueries and CTEs: Mastering Complex SQL Query Architecture is a great bridge from where you are now into that territory.
You should also practice the multi-step analytical mindset: breaking business questions into layers, with each layer computed in a subquery or derived table. Writing Multi-Step Analytical Queries: Chaining Subqueries, JOINs, and GROUP BY to Answer Real Business Questions puts that skill to work on complex, realistic reporting problems.
The "top N per group" pattern will come up again and again throughout your SQL career. You're now equipped to recognize it, reason about it, and write it correctly every time.
SQL Fundamentals