SQL Practice Problem #026 - Find the top product in each category

Problem

Task

Return the highest priced product for each category.

Schema

Table Schema

products(id, title, category, price)

Input

Sample Data

id	title	category	price
1	Keyboard	Accessories	49.99
2	USB Hub	Accessories	59
3	Monitor	Displays	219
4	Portable Display	Displays	180

Output

Expected Output

category	title	price
Accessories	USB Hub	59
Displays	Monitor	219

Answer

Check Your Solution

Show Answer and Explanation

Correct Answer

WITH ranked_products AS (
  SELECT category, title, price,
    ROW_NUMBER() OVER (
      PARTITION BY category
      ORDER BY price DESC
    ) AS price_rank
  FROM products
)
SELECT category, title, price
FROM ranked_products
WHERE price_rank = 1
ORDER BY category;

Explanation

The CTE ranks products inside each category. The outer query keeps rank 1, which is the highest priced product in each category. ORDER BY category makes the final result predictable.

Common Mistakes

Using a single LIMIT 1, which returns only one product overall.
Grouping by category and selecting title without defining which title to keep.
Forgetting PARTITION BY category in the window function.

Concepts

Related Concepts

Window Functions CTE Top N per Group ROW_NUMBER Top N

Next practice