How to count number of consecutive events matching status of last event? In MS-SQL Server 2008, I have a table of events in the following format:

Controller   |  ExecutionTime        |  Result
1            |  2012-09-24 09:00:00  | 0
1            |  2012-09-24 09:01:00  | 0
2            |  2012-09-24 09:02:00  | 1


I need to analyze this table to produce a result that:


Identifies each controller whose latest event was an error (result=1)
Identifies how many consecutive failures that controller has had (# events since the most recent successful event)


I've made some progress toward this task, I can identify the controllers whose most recent event was a failure - see this SQL Fiddle.  But I could use some help finding an approach to counting the # of consecutive failures that has led to this state.

Thanks!
John
Here's one solution. Not very efficient I'm afraid:

SELECT 
    t.Controller,
    MIN(t.ExecutionTime) AS FirstFaultInSeries,
    MAX(t.ExecutionTime) AS LatestFault,
    COUNT(*) AS CntFaults
FROM
  ( SELECT 
        Controller, 
        COALESCE(MAX(CASE WHEN Result = 0 THEN ExecutionTime END)
                ,'19000101')
          AS LatestGood
    FROM t    
    GROUP BY Controller
  ) g
  JOIN t
    ON t.Controller = g.Controller
   AND t.ExecutionTime &gt; g.LatestGood
GROUP BY
    t.Controller ;


Tested: Fiddle

And another:

WITH cte AS
  ( SELECT 
        Controller, ExecutionTime, Result,
        ROW_NUMBER() OVER (PARTITION BY Controller
                           ORDER BY ExecutionTime DESC
                          )
          AS Rn,
        ROW_NUMBER() OVER (PARTITION BY Controller, Result
                           ORDER BY ExecutionTime DESC
                          )
          AS RnResult
    FROM t
  ) 
SELECT 
    Controller,
    MIN(ExecutionTime) AS FirstFaultInSeries,
    MAX(ExecutionTime) AS LatestFault,
    COUNT(*) AS CntFaults
FROM
    cte 
WHERE
    Result = 1
  AND
    Rn = RnResult
GROUP BY
    Controller ;