Return ProdID with max ProdPrice for each OrderID

[Guest]

I am working on a database with a structure similar to an order entry database.
I need help writing a SQL statement analogous to one that will generate
output that lists each order only once, and with each order only the product
from that order that is the most expensive.

For example:
I have constructed a query that will pull together data to look like this:

OrderID ProdID ProdPrice
1 5 10
1 6 12
1 9 7
2 5 10
2 13 25
3 3 5
3 10 10
3 12 8

How do I contsruct a SQL statement to generate this result?

OrderID ProdID ProdPrice
1 6 12
2 13 25
3 10 10


Thanks in advance for help with this.

CM

 

[Jason Lepack]

SELECT
OrderID,
ProdID,
ProdPrice
FROM
YourTableName AS A
INNER JOIN (
SELECT
OrderID,
Max(Price)
FROM
YourTableName
GROUP BY
OrderID) AS B
ON A.OrderID = B.OrderID

Cheers,
Jason Lepack

 

[Guest]

Thanks for your help, Jason, but it didn't work.

Here are some of the data from the original table (actually, a query -
"qryTest"):

OrderID ProdID ProdPrice
1 39400 5.60
1 31622 2.78
2 32651 12.89
2 64421 1.68
3 31622 2.78
3 32657 13.63
3 38746 4.88
3 64421 1.68
4 32480 23.71
4 31622 2.78


Here is the query:

SELECT OrderID,ProdID,ProdPrice
FROM
qryTest AS A
INNER JOIN (
SELECT
OrderID, Max(ProdPrice)
FROM
qryTest
GROUP BY
OrderID) AS B
ON A.OrderID = B.OrderID
ORDER By OrderID


Here is the output:

1 39400 5.60
1 31622 2.78
2 32651 12.89
2 64421 1.68
3 32657 13.63
3 38746 4.88
3 31622 2.78
3 64421 1.68
4 32480 23.71
4 31622 2.78

Any thoughts?

CM

 

[John Spencer]

Try the following modification. If more than one product has the max price
for an order you will get more than one row returned.

SELECT OrderID,ProdID,ProdPrice
FROM
qryTest AS A
INNER JOIN (
SELECT
OrderID, Max(ProdPrice) as TheMax
FROM
qryTest
GROUP BY
OrderID) AS B
ON A.OrderID = B.OrderID
AND A.ProdPrice = B.TheMax
ORDER By OrderID

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Jason Lepack]

Yes, I must have been on crack when I wrote that... it was close but
not quite. This will show ties.

1 39400 5.60
1 31622 2.78
1 31777 5.60

will return:
1 39400 5.60
1 31777 5.60


SELECT
A.OrderID,
A.ProdID,
A.ProdPrice
FROM
qryTest AS A
INNER JOIN (
SELECT
OrderID,
Max(ProdPrice) AS MaxProdPrice
FROM
qryTest
GROUP BY
OrderID) AS B
ON B.OrderID = A.OrderID AND B.MaxProdPrice = A.ProdPrice

 

[Guest]

Thanks very much John. It works perfectly.

CM

Try the following modification. If more than one product has the max price
for an order you will get more than one row returned.

SELECT OrderID,ProdID,ProdPrice
FROM
qryTest AS A
INNER JOIN (
SELECT
OrderID, Max(ProdPrice) as TheMax
FROM
qryTest
GROUP BY
OrderID) AS B
ON A.OrderID = B.OrderID
AND A.ProdPrice = B.TheMax
ORDER By OrderID

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Guest]

Thanks Jason. This one worked perfectly. I appreciate your staying with me on
this one.

CM

 

[Guest]

Well....both worked at first, but after saving the query, closing the
database and reopening, I now get this message:

"The Microsoft Jet database engine cannot find the input table or query
'SELECT OrderID, Max(ProdPrice) AS MaxProdPrice FROM qryTest GROUP BY
OrderID'. Make sure it exists and that its name is spelled correctly"

Here is the SQL statement in total:

SELECT A.OrderID, A.ProdID, A.ProdPrice
FROM qryTest AS A INNER JOIN
[SELECT OrderID, Max(ProdPrice) AS MaxProdPrice
FROM qryTest GROUP BY OrderID] AS B
ON (A.OrderID=B.OrderID) AND (A.ProdPrice=B.MaxProdPrice)
ORDER BY A.OrderID;

Any thoughts?

CM

 

[Guest]

Jason,

Please see my latest post. After saving, closing and reopening the query
doesn't run and I get an error saying it can't find the table or query
assigned to B. What do you think?

CM

 

[Guest]

John,

Please see my other post....after saving, closing and reopening the database
I now get an error in trying to run that query. It says it can't find the
table or query defined by the inner SELECT statement. What happened?

CM

 

[Michael Gramelspacher]

Well....both worked at first, but after saving the query, closing the
database and reopening, I now get this message:

"The Microsoft Jet database engine cannot find the input table or query
'SELECT OrderID, Max(ProdPrice) AS MaxProdPrice FROM qryTest GROUP BY
OrderID'. Make sure it exists and that its name is spelled correctly"

Here is the SQL statement in total:

SELECT A.OrderID, A.ProdID, A.ProdPrice
FROM qryTest AS A INNER JOIN
[SELECT OrderID, Max(ProdPrice) AS MaxProdPrice
FROM qryTest GROUP BY OrderID] AS B
ON (A.OrderID=B.OrderID) AND (A.ProdPrice=B.MaxProdPrice)
ORDER BY A.OrderID;

Any thoughts?

Well, there should be a period after the bracket, ".. OrderID]. AS B"

That is probably only a typo on your part and not the problem. I would try
replacing the brackets with parenthesis and running the query and then try
saving again.

INNER JOIN (SELECT ...OrderID) AS B

 

[Guest]

Thanks. That works, but it didn't seem to like the parenthesis. It wouldn't
take the bracket until I figured out I was missing a space before it (because
it was on the next line).

CM

Well....both worked at first, but after saving the query, closing the
database and reopening, I now get this message:

"The Microsoft Jet database engine cannot find the input table or query
'SELECT OrderID, Max(ProdPrice) AS MaxProdPrice FROM qryTest GROUP BY
OrderID'. Make sure it exists and that its name is spelled correctly"

Here is the SQL statement in total:

SELECT A.OrderID, A.ProdID, A.ProdPrice
FROM qryTest AS A INNER JOIN
[SELECT OrderID, Max(ProdPrice) AS MaxProdPrice
FROM qryTest GROUP BY OrderID] AS B
ON (A.OrderID=B.OrderID) AND (A.ProdPrice=B.MaxProdPrice)
ORDER BY A.OrderID;

Any thoughts?

Well, there should be a period after the bracket, ".. OrderID]. AS B"

That is probably only a typo on your part and not the problem. I would try
replacing the brackets with parenthesis and running the query and then try
saving again.

INNER JOIN (SELECT ...OrderID) AS B