Schema and sample data
| product_id | name | category | sales |
|---|---|---|---|
| 1 | Laptop | Electronics | 1200 |
| 2 | Phone | Electronics | 1500 |
| 3 | Tablet | Electronics | 800 |
| 4 | Monitor | Electronics | 600 |
| 5 | Keyboard | Electronics | 200 |
| 6 | Novel | Books | 300 |
| 7 | Cookbook | Books | 450 |
| 8 | Textbook | Books | 500 |
| 9 | Comic | Books | 150 |
Show setup SQL (copy to run)
CREATE TABLE Products (
product_id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
category VARCHAR(30) NOT NULL,
sales INT NOT NULL
);
INSERT INTO Products (product_id, name, category, sales) VALUES
(1, 'Laptop', 'Electronics', 1200),
(2, 'Phone', 'Electronics', 1500),
(3, 'Tablet', 'Electronics', 800),
(4, 'Monitor', 'Electronics', 600),
(5, 'Keyboard', 'Electronics', 200),
(6, 'Novel', 'Books', 300),
(7, 'Cookbook', 'Books', 450),
(8, 'Textbook', 'Books', 500),
(9, 'Comic', 'Books', 150);From the Products table, return the top 3 best selling products within each category. For every row show the category, the product name, its sales, and the ranking position as sales_rank. Rank products by sales from highest to lowest inside each category. Sort the final result by category, then by sales_rank.
Expected result
| category | name | sales | sales_rank |
|---|---|---|---|
| Books | Textbook | 500 | 1 |
| Books | Cookbook | 450 | 2 |
| Books | Novel | 300 | 3 |
| Electronics | Phone | 1500 | 1 |
| Electronics | Laptop | 1200 | 2 |
| Electronics | Tablet | 800 | 3 |
Show hint
A plain TOP 3 gives you three rows overall, not three per category. Number the rows inside each category first: ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC). Because you cannot use a window function directly in a WHERE clause, wrap it in a CTE and filter the numbering in the outer query.
Try to write the query yourself before you open this.
Show solution and explanation
WITH RankedProducts AS (
SELECT
product_id,
name,
category,
sales,
ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) AS sales_rank
FROM Products
)
SELECT category, name, sales, sales_rank
FROM RankedProducts
WHERE sales_rank <= 3
ORDER BY category, sales_rank;The heart of a "top N per group" problem is numbering the rows separately inside each group. PARTITION BY category restarts the counter for every category, and ORDER BY sales DESC hands number 1 to the biggest seller in that category. The ROW_NUMBER() window function assigns 1, 2, 3, ... with no gaps and no duplicates, so filtering sales_rank <= 3 keeps exactly the top three rows per category. For Electronics that is Phone (1500), Laptop (1200) and Tablet (800); for Books it is Textbook (500), Cookbook (450) and Novel (300).
Why the CTE? A window function is evaluated after WHERE in the logical query order, so you cannot write WHERE ROW_NUMBER() OVER (...) <= 3 directly - SQL Server rejects it. Computing the number in the RankedProducts CTE first, then filtering it in the outer query, is the standard pattern. A subquery in the FROM clause works the same way.
Watch out when sales can tie. ROW_NUMBER() always produces distinct numbers, so if two products in a category share the same sales value it will still pick just one of them for a given position, breaking the tie arbitrarily. If you want every tied product at the cutoff to survive, use RANK() instead: it gives tied rows the same rank (1, 2, 2, 4, ...), so WHERE rnk <= 3 can return more than three rows when there is a tie for third place. DENSE_RANK() is a third option that does not skip numbers after a tie. Choose based on how you want ties handled.
An alternative that avoids a window function entirely is CROSS APPLY with a correlated TOP. First build the distinct list of categories, then for each one pull its three best rows: SELECT c.category, p.name, p.sales FROM (SELECT DISTINCT category FROM Products) c CROSS APPLY (SELECT TOP 3 name, sales FROM Products WHERE category = c.category ORDER BY sales DESC) p;. This can be faster when a supporting index on (category, sales DESC) lets each TOP 3 read only a few rows. See the top N per group scenario for a deeper comparison.