Top N Per Group in SQL

Example query

This example assumes: product_sales(product_id bigint, category text, units_sold numeric). It is written in PostgreSQL syntax, and your table and column names will differ from these.

SELECT product_id, category, units_sold
FROM (
  SELECT product_id,
         category,
         units_sold,
         ROW_NUMBER() OVER (PARTITION BY category ORDER BY units_sold DESC, product_id) AS rn
  FROM product_sales
) ranked
WHERE rn <= 3
ORDER BY category, rn;

How it works

• The inner query numbers products within each category, best first.
• A window function cannot be filtered in the same SELECT, so the numbering sits in a subquery and the outer query keeps rn <= 3.
• Adding product_id to ORDER BY makes the order deterministic when units tie.

MySQL, BigQuery, SQL Server and SQLite differences

• The same query works in PostgreSQL, MySQL 8+, SQL Server, BigQuery and SQLite 3.25+. BigQuery and Snowflake also offer QUALIFY, which filters on a window function without a subquery.

Common mistakes

• Ties at the cut-off: ROW_NUMBER drops some tied products; use RANK or DENSE_RANK if ties should all stay.
• Categories with fewer than N products simply return fewer rows.
• Ranking by a metric over all time can bury new products; filter to a recent window first.

Ask for it in plain English

Instead of writing the query yourself, you can type the question. SchemaChat turns it into SQL against your connected database or uploaded file, runs it, and shows the SQL and the figures it used so you can check the answer before you share it. Check that the query matches the definition your business uses.

• Show the top 3 products by units sold in each category.
• Who are the 5 biggest customers in each region?