Get the Most Expensive Product(s) Per Category
Get the Most Expensive Product(s) Per Category
Products Table:
Categories Table:
Query Explanation
The subquery:
SELECT MAX(p2.UnitPrice) FROM Products p2 WHERE p2.CategoryID = p.CategoryID
finds the highest price within the same category.
-
The main query fetches all products whose price matches that max — this covers multiple most expensive products per category.
-
JOIN Categories: To show the category name for readability.
SQL Query:
SELECT
p.ProductID,
p.ProductName,
c.CategoryName,
p.UnitPrice
FROM
Products p
JOIN
Categories c ON p.CategoryID = c.CategoryID
WHERE
p.UnitPrice = (
SELECT MAX(p2.UnitPrice)
FROM Products p2
WHERE p2.CategoryID = p.CategoryID
)
ORDER BY
c.CategoryName, p.UnitPrice DESC;
Output:
