Brightlane's merchandising team wants the highest-priced product in each product category for a premium catalog review. Products awaiting categorization (those with a missing category_id) are excluded from the analysis.
Write a query to return the product ID, category ID, name, and price for every product whose price equals the highest price within its own category.
Assumptions:
- Products with a missing
category_idshould not appear in the result. - A category's highest price is the largest
priceacross products in thatcategory_id. - A product qualifies when its
priceis exactly equal to its own category's highest price. When multiple products in the same category share the same highest price, every tied product qualifies.
Output:
- One row per qualifying product, with columns
id,category_id,name, andprice.
Schema · ecommerce 5 tables
Run previews · Check grades
Write a query, then run it to see results here.
Worked solution Try it yourself first
SELECT
id,
category_id,
name,
price
FROM
products p
WHERE
price = (
SELECT
MAX(price)
FROM
products inner_p
WHERE
inner_p.category_id = p.category_id
)The shape
The condition is "this product's price equals the highest price within its own category," which is a correlated equality against a per-category \MAX\. The outer \WHERE\ compares each product's \price\ to the maximum across products sharing the same \category_id\, and equality keeps every tied product in the category at that maximum price.
Clause by clause
- \
SELECT id, category_id, name, price\returns the four columns the spec asks for, taken straight from the qualifying product row. - \
FROM products p\reads every product and aliases the table as \p\so the inner subquery can correlate to the outer product's category. - \
WHERE price = (SELECT MAX(price) FROM products inner_p WHERE inner_p.category_id = p.category_id)\is the correlated equality filter. The inner alias \inner_p\distinguishes the inner products rows from the outer \p\. The predicate \inner_p.category_id = p.category_id\ties the inner \MAX\to the same category as the outer row, so every product is compared against the highest price within its own category. Equality rather than \>=\against the maximum is what allows multiple tied products in the same category to all qualify.
The trap
The exclusion of products with a missing \category_id\ is not implemented by an explicit \WHERE p.category_id IS NOT NULL\ clause. It falls out of NULL semantics in the correlated predicate. For a product with \category_id = NULL\, the inner \WHERE inner_p.category_id = p.category_id\ evaluates to NULL for every inner row, because comparing anything to NULL with \=\ yields NULL, not true. The inner subquery returns no rows, \MAX(price)\ over zero rows returns NULL, and the outer \price = NULL\ also evaluates to NULL rather than true. A predicate that is NULL never passes a \WHERE\, so the row is dropped without ever needing an explicit IS NULL filter. The same NULL-propagation logic is also why an attempt to handle the exclusion with \!=\ would silently miss it. \p.category_id != NULL\ is NULL, not true, and would drop every row in the table. The rule is: \IS NULL\ and \IS NOT NULL\ are the only safe predicates against NULL, and a correlated equality predicate against a NULL outer value implicitly excludes that outer row by the same mechanism.
You practiced a correlated subquery whose inner predicate references the outer row's category — each candidate product is compared against the maximum within its own category, with missing-category products implicitly dropped by the predicate's NULL semantics.
