From: "John Mistler" <[EMAIL PROTECTED]> > I have a difficult query for you that has me stumped. The table has two > columns: rowID (MEDIUMINT-but *NOT* AUTO_INCREMENT) and theDate (DATETIME). > I need it to find: > > 1. The COUNT of REPEATED instances of the rowID in the last month.
This is relatively easy: SELECT rowID, COUNT( rowID ) -1 AS countrepeat FROM <tablename> WHERE theDate < CONCAT(DATE_FORMAT(CURDATE(), '%Y-%m-'), '01') AND theDate >= CONCAT(DATE_FORMAT(CURDATE(), '%Y-%m-'), '01') - INTERVAL 1 MONTH GROUP BY rowID HAVING countrepeat >0 See if I have time for #2 later... Regards, Jigal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]