Lynn, I'm curious.

What where you expecting to do with the Years and Tally CTEs? I'm sure that you included them for your tests, but I'd love to know what where you trying.

By the way, this is my code which returns something different which doesn't matter if the streak is longer than 5.

WITH cteStreaks AS(

SELECT *,

Year_From -

ROW_NUMBER() OVER(PARTITION BY Sport, SportEvent, Gender, Country ORDER BY Year_From) * 4 grouper

FROM #mySeries

),

cteMaxStreaks AS(

SELECT Sport,

SportEvent,

Gender,

Country,

MIN(Year_From) Year_From,

MAX(Year_To) Year_To,

COUNT(*) Streak,

ROW_NUMBER() OVER(PARTITION BY Sport, SportEvent, Gender ORDER BY COUNT(*) DESC) rn

FROM cteStreaks

GROUP BY Sport, SportEvent, Gender, Country, grouper

)

SELECT Sport,

SportEvent,

Gender,

Country,

Year_From,

Year_To,

Streak

FROM cteMaxStreaks

WHERE rn = 1

ORDER BY Sport, SportEvent, Gender;

And before I forget, your table has a horrible design. Nvarchar uses twice the space of a varchar so you should use it only when needed. Sport, Event and Country are defined as MAX data types which seems unnecessary when a normal length should be enough (or even an identifier to join to the correct catalogs). Number uses 12 bytes and doesn't even give you the range of an int which only uses 4.

I'm not saying any of this just to make you feel offended, I just want you to realize that this could give you problems in the future. Just try to use the correct data types and lengths according to the data you're storing.