Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Get the products with the highest price

Posted on 2014-11-23
4
Medium Priority
?
134 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

705 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