>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)
>
>
