Level 3 · Top 3 per category, ties included

“Top 3 per category” sounds unambiguous until two products tie for third. Then you have to decide: exactly three rows per category, or everyone who earned a top-3 spot? That is a business decision, and the ranking function is how you encode it.

Two definitions, two functions

• Exactly N rows: ROW_NUMBER() with a tie-breaker (ORDER BY units DESC, product_id). Good for a homepage carousel with three slots.
• Everyone in the top N ranks: RANK(). Tied products share a rank, so a category can return more than N rows. Good for a report where dropping a product would be unfair or misleading.

DENSE_RANK() is a third option: after a tie it does not skip numbers, so “top 3 dense ranks” can return many more products. Use it when you mean “top 3 distinct values”.

Worked example: Lighting in spring 2025

From April to June 2025 (completed orders, positive quantities), Lighting looks like this:

product_id	name	units	RANK	ROW_NUMBER
122	Monroe Glass Desk Lamp	84	1	1
135	Ridge Ceramic Chandelier	44	2	2
127	Dune Black Metal Pendant Light	38	3	3
131	Ashford Linen Pendant Light	38	3	4

With ROW_NUMBER the Ashford pendant disappears only because its id is larger. With RANK both pendants are third, and the full answer has 19 rows: three for five categories and four for Lighting.

The shape

Aggregate, rank in a CTE, filter outside:

ranked AS (
  SELECT *,
         RANK() OVER (PARTITION BY category ORDER BY units DESC) AS units_rank
  FROM product_units
)
SELECT ... FROM ranked WHERE units_rank <= 3

The outer filter is required: WHERE runs before window functions, so WHERE RANK() OVER (...) <= 3 is an error.

QUALIFY

DuckDB, Snowflake, BigQuery and Databricks accept QUALIFY, a filter that runs after window functions, so the extra CTE is not needed:

SELECT category, product_id, name, units,
       RANK() OVER (PARTITION BY category ORDER BY units DESC) AS units_rank
FROM product_units
QUALIFY units_rank <= 3;

Postgres and MySQL do not support it; the CTE works everywhere.

Common mistakes

• Ranking without PARTITION BY. You get the top 3 of the whole store, not of each category.
• ROW_NUMBER without a tie-breaker. The result can change between runs when values tie.
• Ranking before filtering the period. Filter the dates in the aggregate CTE, then rank.

In SQL Server

No QUALIFY, so keep the CTE and filter outside. TOP (3) WITH TIES only works for the whole result, not per group.

SELECT * FROM ranked WHERE units_rank <= 3 ORDER BY category, units_rank, product_id;