Using correlated subquery and MAX

Question

I have two tables:

InventoryPart:

PartNumber, PartDescription, CategoryID, EOQ, StockPrice, ReorderLevel, StockLevel, StockOnOrder, Weight

and

CustOrderLine

OrderID, PartNumber, UnitPrice, OrderQuantity, Discount, Status

I only need to return PartNumber, PartDescription, StockPrice, and CategoryID of the most expensive inventory part in each category

This is the best I have come up with so far...

SELECT IP.PartNumber, IP.PartDescription, IP.CategoryID, IP.StockPrice
FROM InventoryPart IP, CustOrderLine COL
WHERE IP.PartNumber = COL.PartNumber
AND COL.UnitPrice IN
  (SELECT MAX(COL.UnitPrice)
   FROM CustOrderLine COL)

The problem with this code is that it returns the MAX UnitPrice of ALL the categories, instead of separating by category

I'm stuck on this problem and I really appreciate your help.

Answer 1

Something like this should work using a subquery to get your MAX UnitPrice associated with each CategoryId.

SELECT DISTINCT IP.*
FROM InventoryPart IP
   JOIN CustOrderLine COL ON IP.PartNumber = COL.PartNumber
   JOIN (
    SELECT IP.CategoryID, MAX(COL.UnitPrice) MaxPrice
    FROM InventoryPart IP INNER JOIN
        CustOrderLine COL ON IP.PartNumber = COL.PartNumber
    GROUP BY IP.CategoryID
   ) T ON IP.CategoryId = T.CategoryId AND COL.UnitPrice = T.MaxPrice

Here is a reduced Fiddle that shows the inventory parts with the highest unit price per category.