I might be wrong, but this may get you going in the right direction: select count(subject) as count, subject from outgoing where auth='USER' group by subject order by timestamp desc limit 50
This would tell you each subject ever sent by the user, and how many times that subject has been sent. Such as: +-----------+----------------------+ | count | subject | +-----------+----------------------+ | 12 | Subject 1 | | 36 | Subject 2 | | 2 | Another subject | +-----------+----------------------+ And you can narrow it down if you know the subject such as: select count(subject) as count, subject from outgoing where auth='USER' and subject = 'Subject 2' group by subject order by timestamp desc limit 50 This would return +-----------+----------------------+ | count | subject | +-----------+----------------------+ | 36 | Subject 2 | +-----------+----------------------+ I think this is right, if not hopefully my logic is. Regardless, hopefully something can be useful. Dan -----Original Message----- From: Viraj Alankar [mailto:[EMAIL PROTECTED]] Sent: Sunday, March 17, 2002 1:35 PM To: [EMAIL PROTECTED] Subject: sql help examining log table 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