Top N Per Category in SQL (Top 3 Per Group)

The pattern: ROW_NUMBER + PARTITION BY

WITH ranked AS (
  SELECT
    region,
    product,
    revenue,
    ROW_NUMBER() OVER (PARTITION BY region ORDER BY revenue DESC) AS rn
  FROM product_sales
)
SELECT region, product, revenue
FROM ranked
WHERE rn <= 3
ORDER BY region, rn;

PARTITION BY region restarts the numbering at 1 for every region independently, so rn <= 3 keeps exactly the top 3 products by revenue within each region, not just the top 3 rows overall.

Why it has to be wrapped in a CTE

A query's logical processing order runs roughly FROM → WHERE → GROUP BY → window functions → SELECT → ORDER BY. Window functions like ROW_NUMBER() are computed after WHERE has already run, so WHERE rn <= 3 in the same query as the ROW_NUMBER() definition would fail — the value doesn't exist yet at that stage. Computing the row numbers in an inner query (a CTE, as above, or an equivalent subquery) and filtering in an outer query works around that ordering.

RANK vs ROW_NUMBER at the cutoff

-- Two products tied for 3rd place in a region
WITH ranked AS (
  SELECT region, product, revenue,
         ROW_NUMBER() OVER (PARTITION BY region ORDER BY revenue DESC) AS rn,
         RANK()       OVER (PARTITION BY region ORDER BY revenue DESC) AS rnk
  FROM product_sales
)
SELECT * FROM ranked WHERE rnk <= 3;  -- returns 4 rows if two products tie for 3rd

ROW_NUMBER() guarantees exactly 3 rows per region no matter what, arbitrarily picking a winner between tied values. RANK() gives both tied rows the same rank, so filtering on rnk <= 3 can return more than 3 rows per region whenever there's a tie right at the cutoff — see ROW_NUMBER vs RANK vs DENSE_RANK for the full comparison. Which one is correct depends entirely on whether the report needs a fixed row count or needs every genuinely tied value represented.

A correlated-subquery alternative

SELECT p1.region, p1.product, p1.revenue
FROM product_sales p1
WHERE (
  SELECT COUNT(*)
  FROM product_sales p2
  WHERE p2.region = p1.region AND p2.revenue > p1.revenue
) < 3;

This counts how many other products in the same region out-earn the current one; if fewer than 3 do, the current product is in the top 3. It works without window function support, but the subquery re-executes once per outer row, which is typically slower than the ROW_NUMBER() approach on a large table.

Sorting by more than one column

ROW_NUMBER() OVER (
  PARTITION BY region
  ORDER BY revenue DESC, units_sold DESC
) AS rn

Ties on revenue fall through to units_sold as a tiebreaker, exactly like a standard multi-column ORDER BY on a regular query.

Common mistakes

• Trying to filter WHERE row_num <= N in the same SELECT that defines ROW_NUMBER() — it needs an outer query or CTE.
• Using ROW_NUMBER() when a tie at the cutoff should show every tied row — RANK() is the right tool there.
• Forgetting PARTITION BY entirely, which returns the overall top N rather than the top N within each group.
• Reaching for a correlated subquery by default on a large table when the database supports window functions and ROW_NUMBER() would be faster.