Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Get the products with the highest price

Posted on 2014-11-23
4
Medium Priority
?
135 Views
Last Modified: 2014-11-24
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
0
Comment
Question by:ajaeclarke
  • 2
4 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40461373
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
0
 
LVL 25

Accepted Solution

by:
chaau earned 2000 total points
ID: 40461378
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

Open in new window

Please note, that with the query above, when several buyers have the same highest price for the day they will be listed
0
 
LVL 1

Author Comment

by:ajaeclarke
ID: 40461446
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!
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 40461959
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
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

886 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question