C
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.
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.