tblContestResults ContestId Long Integer Score Integer
I didn't want to use a cursor, and I wanted to stay as close to ANSI SQL as possible (so no stored procs, etc.). After googling around a bit, I came across this link that showed how to do this all in SQL using nested selects and virtual groups. The sample was written to be compatible with MSSQL. After tweaking it just a bit, I came to this solution, which works wonderfully.
SELECT Max(grpCnt) AS MaxRun FROM (Select grp, Count(*) As GrpCnt From (Select A.ContestId, A.Score, Case When A.Score <= -20 Then Isnull( (Select Max(B.ContestId) From tblContestResults As B Where B.ContestId < A.ContestId and B.Score>-20), (Select Min(C.ContestId) from tblContestResults AS C) ) End As grp From tblContestResults As A) As WithGrps Where grp Is Not Null Group By grp) AS WithGrpCnts;
You can take each of the nested selects out and break them apart to execute them in order to see how the query is built up. Quite an interesting technique, and one that I will definitely keep in my bag of tricks. For my purposes, I had to use MS Access, so that meant I had to change things in the SQL a bit more since there is no "CASE WHEN" in Access. The following is the code I ended up using, and it also works quite well. I didn't use the nz() function because there is a problem in Delphi's TADODataset when executing code that has that expression in it (more on that later). Using nz() does work in Access, and makes the query easier to read, IMO. But if it doesn't work in Delphi, then I can't use it.
SELECT MAX(grpCnt) AS MaxRun FROM (SELECT grp, Count(*) as GrpCnt FROM (SELECT A.ContestID, A.Score, IIf(A.Score<=-20, IIf( isnull((Select Max(B.ContestId) From tblContestResults As B Where B.ContestId < A.ContestId and B.Score>-20)) ,(Select Min(C.ContestId) from tblContestResults AS C) ,(Select Max(B.ContestId) From tblContestResults As B Where B.ContestId < A.ContestId and B.Score>-20) ) ,Null) AS grp FROM tblContestResults AS A ORDER BY ContestId) as WithGrps WHERE grp is not null GROUP BY grp) AS WithGrpCnts;
Disclaimer The opinions expressed herein are my own personal opinions and do not represent my employer's view in any way.