Hi Viraj, You can do it using temporary table.
Create temporary table tmp select subject from outgoing where auth='USER' order by timestamp desc limit 50; Select count(distinct subject) from tmp group by subject; drop table tmp; If the result of the second query is 1 all the last 50 messages have the same subject. It is assumed there are at least 50 rows for auth = 'USER'. Regards Anvar. At 03:34 PM 17/03/2002 -0500, you wrote: >Hello, > >We use mysql to store outgoing email headers from our users and do throttling >on users that appear to be spamming based on some simple queries to this >table. We use the Communigate mail server and this throttling script is a PERL >program implemented as a content filter. More information is here for those >interested: > >http://www.cse.fau.edu/~valankar/ > >I am trying to figure out what is the best way to do a certain query. My >outgoing log table looks like this: > >mysql> desc outgoing; >+-----------+----------------------+------+-----+---------+-------+ >| Field | Type | Null | Key | Default | Extra | >+-----------+----------------------+------+-----+---------+-------+ >| rpath | varchar(80) | YES | | NULL | | >| auth | varchar(80) | | MUL | | | >| ip | varchar(80) | YES | | NULL | | >| hfrom | varchar(80) | YES | | NULL | | >| hto | varchar(80) | YES | | NULL | | >| subject | varchar(80) | YES | | NULL | | >| messageid | varchar(80) | YES | | NULL | | >| timestamp | timestamp(14) | YES | | NULL | | >| rcpts | smallint(5) unsigned | YES | | 0 | | >+-----------+----------------------+------+-----+---------+-------+ > >What I would like to do is reject a message if the last 50 messages have the >same subject. > >In other words, I want to look at the 50 latest entries in this table for a >certain user (identified by the auth field) to find out if all of these >messages have the same subject. > >The only way I can think of doing this is basically: > >select subject from outgoing where auth='USER' order by timestamp desc >limit 50 > >And then going through each one of these rows in my program to see if they are >all the same subject. Is there a way I can do this logic in the select query >instead? > >Thanks, > >Viraj. > >--------------------------------------------------------------------- >Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > >To request this thread, e-mail <[EMAIL PROTECTED]> >To unsubscribe, e-mail ><[EMAIL PROTECTED]> >Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
