Wicked Smart Data
LearnInsightsAboutContact
Sign InLet's Build
LearnInsightsAboutContact
Sign InLet's Build
Wicked Smart Data

Intelligence, automation, and expert execution — plus an elite library of free knowledge. We turn complexity into competitive advantage.

Start a conversation

Platform

  • Learning Paths
  • Insights
  • RSS Feed

Company

  • About
  • Contact
  • Work With Us

Legal

  • Privacy Policy
  • Terms of Service

© 2026 Wicked Smart Data. All rights reserved.

Intelligence · Automation · Advantage

All Insights
SQL

Writing SQL to Find the Top N Rows Per Group Using Subqueries and Derived Tables

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.

⚡ Practitioner22 min readNov 22, 2026Updated Nov 22, 2026
Writing SQL to Find the Top N Rows Per Group Using Subqueries and Derived Tables
On this page
  • Introduction
  • Prerequisites
  • The Core Insight: Ranking as Counting
  • Setting Up the Sample Schema
  • Step 1: Build the Correlated Subquery That Counts Competitors
  • Step 2: Filter Using a Derived Table (Inline View)
  • Step 3: Understanding Why This Works (And Why the Filter Goes Outside)
  • Handling Ties: The Critical Edge Case
  • A More Complex Scenario: Top N with Additional Filtering and Joining
  • Alternative: Self-Join Approach
  • Parameterizing N: Making the Query Reusable
  • Performance Implications
  • Hands-On Exercise
  • Common Mistakes & Troubleshooting
  • Summary & Next Steps

Writing SQL to Find the Top N Rows Per Group Using Subqueries and Derived Tables

Introduction

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:

  • How to frame "top N per group" as a counting problem that SQL can solve
  • How to use a correlated subquery to rank rows without window functions
  • How to wrap that logic in a derived table (inline view) to make filtering clean and readable
  • How to handle ties, nulls, and edge cases that break naive implementations
  • When this subquery-based approach is the right tool, and when you should reach for something else

Prerequisites

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.


The Core Insight: Ranking as Counting

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.


Setting Up the Sample Schema

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.


Step 1: Build the Correlated Subquery That Counts Competitors

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.


Step 2: Filter Using a Derived Table (Inline View)

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.


Step 3: Understanding Why This Works (And Why the Filter Goes Outside)

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:

  1. Inner query computes the ranking metric
  2. Outer query filters on it

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.


Handling Ties: The Critical Edge Case

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:

  • Both products at $62,000 see the same competitors_above count (1, since only the $85,000 product beats them)
  • Both qualify as "top 3" (competitors_above < 3)
  • You'd get 4 results for Electronics instead of 3

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.


A More Complex Scenario: Top N with Additional Filtering and Joining

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.


Alternative: Self-Join Approach

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.


Parameterizing N: Making the Query Reusable

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.


Performance Implications

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:

  • 10,000 products across 50 categories: the correlated subquery runs 10,000 times
  • Each run of the subquery scans or seeks within the same tables

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:

  1. 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.

  2. 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.

  3. 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.


Hands-On Exercise

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.


Common Mistakes & Troubleshooting

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:

  1. Does your WHERE filter in the outer query match data that actually exists in the derived table result?
  2. Is competitors_above being computed correctly? Run the inner query alone (without the outer WHERE) and inspect the counts.
  3. Did you accidentally use <= 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.


Summary & Next Steps

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:

  • Strict > for RANK-style ties (tied rows both qualify)
  • Secondary tiebreaker in the comparison for ROW_NUMBER-style (exactly N rows)
  • Mirror all outer filters inside the correlated subquery
  • Use derived table aliasing consistently

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.

Work With Us

From insight to implementation

Reading is the start. When you're ready to build the data, automation, or AI systems behind it, our team turns strategy into shipped results.

Let's Build

SQL Fundamentals

Previous

Reading Data from Multiple Tables: Writing Your First Multi-Table SELECT with JOIN

Related Insights

SQLExpert

Writing SQL to Detect and Resolve Parameter Sniffing and Plan Cache Instability

30 min
SQLFoundation

Reading Data from Multiple Tables: Writing Your First Multi-Table SELECT with JOIN

16 min
SQLPractitioner

Writing SQL to Rank and Deduplicate Rows Using ROW_NUMBER in a CTE: Partition, Filter, and Delete Duplicates in a Single Query

23 min

On this page

  • Introduction
  • Prerequisites
  • The Core Insight: Ranking as Counting
  • Setting Up the Sample Schema
  • Step 1: Build the Correlated Subquery That Counts Competitors
  • Step 2: Filter Using a Derived Table (Inline View)
  • Step 3: Understanding Why This Works (And Why the Filter Goes Outside)
  • Handling Ties: The Critical Edge Case
  • A More Complex Scenario: Top N with Additional Filtering and Joining
  • Alternative: Self-Join Approach
  • Parameterizing N: Making the Query Reusable
  • Performance Implications
  • Hands-On Exercise
  • Common Mistakes & Troubleshooting
  • Summary & Next Steps