# Return ProdID with max ProdPrice for each OrderID

G

#### 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

J

#### 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

G

#### 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

J

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

J

#### 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

G

#### Guest

Thanks very much John. It works perfectly.

CM

John Spencer said:
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
..

G

#### Guest

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

CM

G

#### 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

G

#### 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

G

#### 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

M

#### 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

G

#### 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

Michael Gramelspacher said:
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