Max query

[ocp&mcsd]

Dear all,

How are you??

I have a small question…

I have the below table:

CustomerName ProductName Price
John W 100
John X 120
David Y 80
David Z 60

I want a query to return back the maximum price product for each customer as follow:

CustomerName ProductName Price
John X 120
David Y 80

Thanks in advance

 

[KenHigg]

Seems you could simply use a totals query grouping on name & product and max'ing the price...

???
ken

 

[ocp&mcsd]

Sorry..
It doesn't work...

 

[KenHigg]

Sorry - I mis-read your question.

Let me think for a few minutes...

 

[Brianwarnock]

This I think takes 2 queries, 1 group on customer and max on price then join this back to table in query 2 joining on price to select all the info.

Brian

 

[ocp&mcsd]

This I think takes 2 queries, 1 group on customer and max on price then join this back to table in query 2 joining on price to select all the info.

Brian

How?

How this SQL statement can be written??

 

[KenHigg]

This I think takes 2 queries, 1 group on customer and max on price then join this back to table in query 2 joining on price to select all the info.

Brian

That's what I was thinking... I thought there may be another way but I can't think of any at the moment...


ken