asked on

Get the products with the highest price

Hi
Everyday we enter buyers that put prices out for products. We enter the prices for each product they list.

What I am trying to do is create a query that will show which buyer has the highest price for each product entered daily.

For example the data looks like this:

BuyerCode CodePD Price Date
AA H1 $280 24/11/14
GH H1 $295 24/11/14
GG H1 $285 24/11/14
SG H1 $275 24/11/14
AA CO $300 24/11/14
GG CO $305 24/11/14
and so on and so on........

What I would like it to be able to list the highest price details:
BuyerCode CodePD Price Date
GH H1 $295 24/11/14
GG CO $305 24/11/14
etc etc
If a buyer has the same price then that should show in the results.

I hope I have explained it properly....I can clarify if need be.

Thanks in advanced.

Ajae

Rey Obrero (Capricorn1)

try this query

select T.*
from TableX as T
inner join
(select max(t2.Price) as maxPrice, t2.CodePD, t2.[Date]
from tableX as t2
group by t2.CodePD, p2.[Date]) as t3
on T.CodePD=t3.CodePD and t3.[Date]=t3.[Date] and T.Price=t3.maxPrice

ASKER CERTIFIED SOLUTION

chaau

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ajaeclarke

ASKER

Thanks for the replies.

Rey Obrero, I get a join expression not supported.

Chaau, with a bit of tweaking I got it working how I needed.

Thanks for you help!

Rey Obrero (Capricorn1)

typo on the join, t3.[Date]=t3.[Date] should be T.[Date]=t3.[Date]

select T.*
from TableX as T
inner join
(select max(t2.Price) as maxPrice, t2.CodePD, t2.[Date]
from tableX as t2
group by t2.CodePD, p2.[Date]) as t3
on T.CodePD=t3.CodePD and T.[Date]=t3.[Date] and T.Price=t3.maxPrice