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
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
