return product with maximum price
in [Queries]
|
Prev: update query
Next: Not In (SELECT DISTINCTROW
From: CuriousMark on 25 Mar 2010 12:19 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: Daryl S on 25 Mar 2010 15:10 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.
From: CuriousMark on 25 Mar 2010 22:14 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.
From: John Spencer on 26 Mar 2010 08:31 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: Daryl S on 26 Mar 2010 10:17
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. |