CODEWITHSUNDEEP
sqlmaxcorrelated-subquery
Christophe
Christophe

Reputation: 147

Using correlated subquery and MAX

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.

Upvotes: 1

Views: 8276

Answers (2)

sgeddes
sgeddes

Reputation: 62851

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.

Upvotes: 1

John Woo
John Woo

Reputation: 263833

here's a simple version.

SELECT  a.PartNumber, a.PartDescription, a.CategoryID, a.StockPrice
FROM    InventoryPart a
        INNER JOIN
        (
            SELECT  PartNumber, MAX(StockPrice) max_price
            FROM    Inventorypart
            GROUP   BY PartNumber
        ) b ON  a.partNumber = b.PartNumber AND
                a.StockPrice = b.max_price

for better performance, add an INDEX on columns (PartNumber, StockPrice)

ALTER TABLE InventoryPart ADD INDEX (PartNumber, StockPrice)

UPDATE 1

SELECT  a.PartNumber, a.PartDescription, a.CategoryID, a.StockPrice
FROM    InventoryPart a
        INNER JOIN
        (
            SELECT  PartNumber, CategoryID, MAX(StockPrice) max_price
            FROM    Inventorypart
            GROUP   BY PartNumber, CategoryID
        ) b ON  a.partNumber = b.PartNumber AND
                a.StockPrice = b.max_price AND
                a.CategoryID = b.CategoryID

and

ALTER TABLE InventoryPart ADD INDEX (PartNumber, StockPrice, CategoryID)

Upvotes: 1

Related Questions