Trouble with correlated subquery, example using Northwind RRS feed

  • Question

  • User465392828 posted
    Suppose I have this query, which shows each order and the price of the most expensive item in each order:

    SELECT Ord.OrderID, Ord.OrderDate, 
        MAX(OrdDet.UnitPrice) AS maxUnitPrice
    FROM Northwind.dbo.[Order Details] AS OrdDet 
        INNER JOIN 
        Northwind.dbo.Orders AS Ord
        ON Ord.OrderID = OrdDet.OrderID
    GROUP BY Ord.OrderID, Ord.OrderDate

    I need to also show the ProductID that has MaxUnitPrice from the Order Details.  I can't just add ProductID to the select list because I'd have to group by it, and then I'd get a row for each product, instead of a row for each order... I think I need a correlated subquery but can't work out how to do it!

    Saturday, August 13, 2005 12:28 PM

All replies

  • User-436472924 posted
    Hello Celestine.. not sure if you still need a answer.. but here is some help... it does give 6 extra records 
    due the UnitPrice being the same.
    SELECT  Ord.OrderID, OrdDet.ProductID, Ord.OrderDate, OrdDet.UnitPrice AS UnitPrice

    FROM dbo.[Order Details] AS OrdDet INNER JOIN

    dbo.Orders AS Ord ON Ord.OrderID = OrdDet.OrderID inner join

    (select OrderID, MAX(UnitPrice) as UnitPrice
    FROM dbo.[Order Details]
    Group by OrderID) as fb ON Ord.OrderID = fb.OrderID AND fb.UnitPrice = OrdDet.UnitPrice
    Tuesday, August 30, 2011 7:07 AM