Please try my solution before you tell me it's broken, OK? I know you want to see the top 10 spammers for EACH day. That's what I wrote for you. Please try my solution with your data and get back to me with the results and explain to me what's wrong so I can fix it.
Thank you for your patience, Shawn Green Database Administrator Unimin Corporation - Spruce Pine Seena Blace <[EMAIL PROTECTED]> wrote on 05/16/2005 04:41:19 PM: > Shawn, > query > SELECT * > FROM spam_stats > WHERE rank <= 10; > will return all rows which I don't want. > I need datewise top 10 spam domain. > means condition would be serach those rows which are having top 10 > spam (means highest) on each day and show the output like which I > send earlier. > thanks > > [EMAIL PROTECTED] wrote: > Seena Blace wrote on 05/16/2005 11:13:48 AM: > > > shawn > > I think 2nd query will return only 10 rows. > > I want out like followings > > date domain spam > > 05/05/05 hotmail.com 120 > > 05/05/05 yahoo.com 110 > > 05/05/05 abc.com 99 > > 05/05/05 def.com 80 > > 05/05/05 mnpo.net 79 > > ......................................... like that upto 10 > > -------------------------------------------------- > > 05/06/05 yahoo.com 300 > > 05/06/05 def.com 250 > > 05/06/05 zer.com 200 > > ..................................like that upto 10 > > > > Each day there are multiple entry from diffrent domains or same domain. > > I want each day whatever top 10 spam sender domain. > > thanks > > > > > > > > > > [EMAIL PROTECTED] wrote: > > Seena Blace wrote on 05/16/2005 10:08:15 AM: > > > > > Any suggestion pl? > > > > > > Seena Blace wrote:hi, > > > here is table description > > > report1 > > > > > > +-------------------------+------------------+------+----- > > > +---------------------+----------------+ > > > | Field | Type | Null | Key | Default | Extra | > > > +-------------------------+------------------+------+----- > > > +---------------------+----------------+ > > > | id | int(10) unsigned | | PRI | NULL | auto_increment | > > > | host_id | int(10) unsigned | | MUL | 0 | | > > > | report_rcpt_domain_id | int(10) unsigned | YES | MUL | NULL | | > > > | report_sender_domain_id | int(10) unsigned | YES | MUL | NULL | | > > > | report_ipaddress_id | int(10) unsigned | YES | MUL | NULL | | > > > | time | datetime | | MUL | 0000-00-00 00:00:00 | | > > > | detected_spam | int(10) unsigned | | | 0 | | > > > | detected_virus | int(10) unsigned | | | 0 | | > > > | processed | int(10) unsigned | | | 0 | | > > > | allowed | int(10) unsigned | | | 0 | | > > > | suspected | int(10) unsigned | | | 0 | | > > > | blocked | int(10) unsigned | | | 0 | | > > > | spam | int(10) unsigned | | | 0 | | > > > | virus | int(10) unsigned | | | 0 | | > > > > > > > > > I WANT REPORT LIKE FOLLOWINGS > > > > > > date sender processed spam suspected > > > > > > > > > I want top 10 spam sender each day. > > > > > > QUery i'm using > > > select date_format(time,'%Y-%d-%m'),report_sender_domain_id, > > > processed ,spam from report1 > > > order by spam desc ,report_sender_domain_id,date_format(time,'%Y-% > > > d-%m') limit 10; > > > > > > > > > > > > Please suggest. > > > thanks > > > > > It was the weekend. Not everyone lurks on their days off. > > > > Try this > > select date_format(time,'%Y-%d-%m') > > ,report_sender_domain_id > > ,processed > > ,spam > > ,suspected > > from report1 > > order by spam desc > > ,report_sender_domain_id > > ,date_format(time,'%Y-%d-%m') > > limit 10; > > > > or if there is more than one entry per spammer per day > > > > SELECT date_format(time,'%Y-%d-%m') > > ,report_sender_domain_id > > ,sum(processed) as processed > > ,sum(spam) as spam > > ,sum(suspected) as suspected > > FROM report1 > > GROUP BY date_format(time,'%Y-%d-%m') > > ,report_sender_domain_id > > ORDER BY spam desc > > ,report_sender_domain_id > > ,date_format(time,'%Y-%d-%m') > > LIMIT 10; > > > > That will give you their total stats for each day. > > > > Shawn Green > > Database Administrator > > Unimin Corporation - Spruce Pine > > > > --------------------------------- > > OK, then what you want to do will take two steps and another table (unless > you want to write application code) to do with MySQL; > > First step is to create a table where the intermediate results can be > stored. What this table does is number each row for each day. Since it > appears that you may want this information frequently (but only need to > update it at the end of every day) I will make this a permanent table (not > a temporary table). > > CREATE TABLE spam_stats ( > report_date date not null, > report_sender_domain_id int unsigned not null, > rank int unsigned auto_increment, > processed int unsigned, > spam int unsigned, > suspected int unsigned, > PRIMARY KEY (report_date, rank), > UNIQUE KEY (report_sender_domain_id, report_date), > KEY (rank) > ); > > The UNIQUE key ensures that the same spammer cannot have more than one > entry per day while the PRIMARY KEY allows for a groupwize autonumber > (each entry per day gets it's own number, rank, starting at 1). The last > key is optional but will seriously speed up the returns for the report you > wanted. Next we need to populate our new table with the "spam" report data > (filled in from most spam to least spam per domain per day). > > INSERT spam_stats (report_date, report_sender_domain_id, processed, spam, > suspected) > SELECT date(`time`) > ,report_sender_domain_id > ,sum(processed) as processed > ,sum(spam) as spam > ,sum(suspected) as suspected > FROM report1 > GROUP BY date(`time`) > ,report_sender_domain_id > ORDER BY spam desc > ,report_sender_domain_id > ,date(`time`); > > What will happen is that the rank column will be automatically filled in > for each day's spam statistics. Then to get your "top ten" list you just > run: > > SELECT * > FROM spam_stats > WHERE rank <= 10; > > Update spam_stats at the end of each new day and will only need to > calculate those values once. If you create more tables like spam_stats for > your other statistics (processed, suspected, blocked, etc.), you will be > able to do rank-based reporting for those statistics, too ("top 20 sites > blocked", for example). > > If you play around with the general idea of the groupwize numbering > feature of auto_increment + primary key, I am sure you can solve a lot of > your reporting issues. > > Shawn Green > Database Administrator > Unimin Corporation - Spruce Pine > Yahoo! Mail > Stay connected, organized, and protected. Take the tour