How to find top 25 selling products for each day of year?

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)


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: How to find top 25 selling products for each day of year?

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

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org