return product with maximum price

[CuriousMark]

How do I write a query that returns the product from each order that has the
highest price? Each order can have multiple products, so there is an
intersection table that links the Orders to the Products. Here are the
relevant tables:

tblOrders
OrderID (pk)
CustID (fk)

tblOrderProd
OrderProdID (pk)
OrderID (fk)
ProdID (fk)

tblProducts
ProdID (pk)
ProdPrice

For each order there are several products. I can write a query to produce
this table:

Order ProductID Price
1 101 $12.00
1 332 $6.00
1 234 $21.00
2 324 $3.50
2 101 $12.00
2 132 $8.00
3 154 $3.95
3 256 $25.50
3 221 $2.50

....and of course, sort the result based on OrderID first, then Price second.

But how do I write a query to produce a table that lists the most expensive
product for each order?

OrderID ProductID
1 234
2 101
3 256

Thanks.

 

[Daryl S]

CuriousMark -

Start with this (your column titles did not match the field names in the
tables, so I used the field names in the tables - adjust if needed):

SELECT tblOrderProd.OrderID, tblOrderProd.ProdID FROM
tblOrderProd INNER JOIN tblProducts ON tblOrderProd.ProdID =
tblProducts.ProdID
WHERE tblProducts.ProdPrice = (SELECT Max(ProdPrice) FROM tblProducts AS tP2
WHERE tP2.ProdID = tblProducts.ProdID)

 

[CuriousMark]

Thanks but not quite.....this gives me a record for each Product in each
Order instead of just the one product with the highest price for each order.
You are correct about the labelling, but you guessed correctly.

 

[John Spencer]

This query will give you the Max price for each Order.

SELECT OP.OrderID
, Max(P.Price) as MaxPrice
FROM tblOrderProd AS OP INNER JOIN tblProducts As P
On OP.ProdID = P.ProdID
GROUP BY OP.OrderID

You should be able to use that to match up the price with the items in the
order. Of course if there happen to be two items in the order with the same
maximum price, you will get two records returned for that order.

You can try the following UNTESTED query and see if it gives you the desired
result.

SELECT O.OrderID, O.CustID, P.ProdID, P.Price
FROM ((tblOrders as O INNER JOIN tblOrderProd as OP
ON O.OrderID = P.OrderID)
INNER JOIN tblProducts as P
ON OP.ProdID = P.ProdID)
INNER JOIN
(
SELECT OP.OrderID, Max(P.Price) as MaxPrice
FROM tblOrderProd AS OP INNER JOIN tblProducts As P
ON OP.ProdID = P.ProdID
GROUP BY OP.OrderID
) as q
ON OP.OrderID = q.OrderID
WHERE P.Price = q.MaxPrice

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[Daryl S]

CuriousMark -

Yes, that would be right... Try this (untested):

SELECT tblOrderProd.OrderID, tblOrderProd.ProdID FROM
tblOrderProd INNER JOIN tblProducts ON tblOrderProd.ProdID =
tblProducts.ProdID
WHERE tblProducts.ProdPrice = (SELECT Max(ProdPrice) FROM tblProducts AS tP2
INNER JOIN tblOrderProd AS tOP2 ON tOP2.ProdID = tP2.ProdID
WHERE tOP2.OrderID = tblOrderProd.OrderID)

 

[CuriousMark]

Thanks Johh. I had figured out the first solution with creating a second
query and then third to merge the two and produce the final result, but I was
hoping there was a simpler way without so many nested queries. It eats up
processor time.

 

[CuriousMark]

Yes! Worked. And it is faster than the nested queries that I managed to put
together. I will have to study what you did. Thanks very much.