From: Peter Brawley
Date: May 8 2011 6:03pm
Subject: Re: How to find top 25 selling products for each day of year?
List-Archive: http://lists.mysql.com/mysql/224971
Message-Id: <4DC6DAEB.6040606@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

 >I would like to create a table with the top 25 Amt_Sold products for 
each day

Examples at "Top N per group" at http://www.artfulsoftware.com/queries.php

PB

-----

On 5/8/2011 12:21 PM, mos wrote:
> I have a table (MyISAM) with summarized Sales data:
>
> Table: ProdSales
> Columns:
> Sales_Date   Date,
> Product_Code Char(10),
> Amt_Sold  Double
>
>
> There are approx 5,000 products sold each day and there are 3 years 
> worth of data. I would like to create a table with the top 25 Amt_Sold 
> products for each day
>
> Example:
>
> '2011-03-01', "ABC001", 30421.21
> '2011-03-01', "ABC031", 30000.15
> '2011-03-01', "ABC011", 23312.00
> '2011-03-01', "ABC101", 22211.87
> '2011-03-01', "DE0211", 21931.44
> '2011-03-01', "AGC331", 20321.32
> '2011-03-01', "DEF321", 20300.31
> '2011-03-01', "KLC031", 20000.21
> '2011-03-01', "MIU031", 19332.00
> ....
> 25th top Amt_Sold for 2011-03-11
>
> '2011-03-02', "FER001", 40421.21
> '2011-03-02', "DEC031", 40010.15
> etc..
>
>
> So the table would have 25 rows per date and there would be several 
> years worth of data.
>
> Now I can do this easy enough for one date. But is there an efficient 
> way of doing this for each day of the year without resorting to 
> executing the same SQL statement for each day?
>
> TIA
> Mike
> (MySQL 5.5)
>
>