MySQL Forums
Forum List  »  Newbie

Using MAX() to select highest values, but how to get other columns to match
Posted by: Kyle Johnson
Date: March 14, 2011 05:57PM

I have a table of sales reports for each day for a number of store locations. I'm trying to create a view that finds the highest day of sales for each location.

Here is my current SQL code:

CREATE VIEW sales_records AS
  SELECT dailyreports.store_id,
  MAX(dailyreports.sales) AS max_net_sales,
  dailyreports.date_report
  FROM dailyreports
  GROUP BY dailyreports.store_id;

While this correctly identifies the top sales day, the date_report column is simply choosing the very first date in the data for each store_id. How do I get the date_report column to align with the max_net_sales? That is, I want to know what date the MAX sales occurred.

Example Data:

store_id | sales | date_report
1          100     2009-05-22
1          150     2010-08-25
1          95      2010-08-26

If I ran the view on the data above, the resulting set would be:

store_id | sales | date_report
1          150     2009-05-22

But I want it to be:

store_id | sales | date_report
1          150     2010-08-25

Thanks for the help!



Edited 1 time(s). Last edit at 03/14/2011 06:03PM by Kyle Johnson.

Options: ReplyQuote


Subject
Written By
Posted
Using MAX() to select highest values, but how to get other columns to match
March 14, 2011 05:57PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.