I had a need to write SQL to answer the question "How many times in a row have I scored -20 or better?" in a game of Golden Tee. I have the raw data stored in a table that has these fields (and more, but they aren't important to this exercise).

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;