return product with maximum price
in [Queries]
|
Prev: update query
Next: Not In (SELECT DISTINCTROW
From: CuriousMark on 26 Mar 2010 12:46 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. "John Spencer" wrote: > 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 > > CuriousMark wrote: > > 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. > . >
From: CuriousMark on 29 Mar 2010 09:16
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. "Daryl S" wrote: > 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) > > -- > Daryl S > > > "CuriousMark" wrote: > > > 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. > > > > "Daryl S" wrote: > > > > > 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) > > > > > > -- > > > Daryl S > > > > > > > > > "CuriousMark" wrote: > > > > > > > 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. |