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:

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

Ajae
LVL 2
Who is Participating?

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Commented:
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
Commented:
you need to use the query similar to this:
Select
``````prices.BuyerCode, prices.CodePD, prices.Price, prices.Date
FROM prices INNER JOIN
(SELECT CodePD, Max(Price) AS MaxPrice, Date
FROM prices GROUP BY CodePD, Date) AS m
ON prices.CodePD = m.CodePD AND prices.Date = m.Date
ORDER BY 3 DESC, 1, 2
``````
Please note, that with the query above, when several buyers have the same highest price for the day they will be listed

Experts Exchange Solution brought to you by

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Author Commented:
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!
Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.