A Brightlane category manager is auditing the catalogue and wants to review products in category-by-category blocks, with the most expensive items visible first inside each category.
Write a query to return each product's name, category ID, and price.
Assumptions:
- The
productstable contains every product in Brightlane's catalogue. - Sort keys mix directions:
category_idascending groups the products into blocks, andpricedescending orders each block from most to least expensive. - When two products inside the same category share the same
price, the product with the loweridshould appear first.
Output:
- One row per product, with columns
name,category_id, andprice, sorted bycategory_idascending, thenpricedescending, thenidascending.
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
name,
category_id,
price
FROM
products
ORDER BY
category_id,
price DESC,
idThe shape
Three sort keys with mixed directions produce the layout in one pass — category_id ascending groups the catalogue into category blocks, price DESC ranks each block from most to least expensive, and id ascending stabilises any remaining ties.
Clause by clause
SELECT name, category_id, pricereturns the three columns the category manager wants to review.FROM productsreads the catalogue.ORDER BY category_id, price DESC, idcarries a different direction on each key. The first key,category_id, defaults to ascending and produces contiguous blocks per category. The second key,price DESC, reverses direction so that inside each category block, the most expensive item comes first. The third key,id, defaults to ascending and only fires when two products in the same category share a price.
Why this and not ORDER BY category_id DESC, price DESC, id
The direction on each sort key is independent. DESC on price does not propagate to category_id or to id. A learner reading the prompt and seeing the word "most expensive first" might be tempted to put DESC on every key for consistency, but the prompt actually asks for categories in ascending order (the natural way to read a category-by-category audit) and prices in descending order within each one. The clause says exactly that, key by key.
The key-by-key direction is what makes the "group-then-rank-within-group" layout possible in a single clause. Group by the first key, rank by the second, stabilise with the third. Once the pattern clicks, it generalises to any number of nesting levels — and to any mix of directions across them.
You practiced mixing sort directions across multiple keys — each ORDER BY key carries its own ASC or DESC. Mixing directions is how you express a 'group by one thing, then rank within each group' layout in a single query.
