Jochem - you are a rock star - thanks for this! Performance is not an issue - I can run this once or twice a month late, late at night :)
-----Original Message----- From: Jochem van Dieten [mailto:[EMAIL PROTECTED]] Sent: Sunday, February 02, 2003 12:04 PM To: CF-Talk Subject: RE: 2nd try: SQL query help Quoting Josh Trefethen <[EMAIL PROTECTED]>: > > Sounds like a job for a stored proc....I don't think a simple query will > do it for you. Simple was not a requirement. I just hope performance is not a requirement either ;-) SELECT MIN(t1id) AS minimum, t2id AS maximum, MAX(occurences) AS occurences FROM ( SELECT t1id, MAX(t2id) AS t2id, MAX(occurences) AS occurences FROM ( SELECT t1.id AS t1id, t2.id as t2id, count(t3.id) + 2 as occurences FROM test t1, test t2, test t3 WHERE t1.id < t3.id AND t2.id > t3.id AND t1.myTinyint = 0 AND t2.myTinyint = 0 AND t3.myTinyint = 0 GROUP BY t1.id, t2.id HAVING COUNT(t3.id) > 5 -- Fill out (minimum count - 2) AND COUNT(t3.id) = t2.id -1 -t1.id ) AS sub GROUP BY t1id ) AS sup GROUP BY t2id Jochem ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
