Counting consecutive values using partitioning - gaps and islands?

user_6257317 0
Viewed 149

I'm starting to learn row_number() and over() with partitioning, and I ran into something on this site called Gaps and Islands... I'm trying to understand it all.

My data table:

Step Number Status
1 Passed
2 Passed
3 Passed
4 Passed
5 Failed
6 Failed
7 Failed
8 Failed
9 Failed
10 Passed
11 Passed
12 Passed

The results I'm trying to get:

Order Status Consecutive Counts
1 Passed 4
2 Failed 5
3 Passed 3

This is as far as I've gotten. The first "Passed" doesn't show up in the recordset, and I can't find where to put a COUNT() or whatever function to get the number of rows in a partition.

SELECT
    ROW_NUMBER() OVER(ORDER BY Status) AS 'Order', Status
FROM (
    SELECT *, Steppy = row_number() over (partition by [Status] order by [StepNumber] desc)
    FROM Q1
) A
GROUP BY Status

Results:

Order Status
1 Failed
2 Passed
window-functions tsql sql-server sql
1 Answers

The following gives your desired results.

Using your step number we can create a sequence from which you can subtract the row-number of each partition to greate a group, then you can aggregate by this group:

with g as (
  select StepNumber, status, 
    Row_Number() over(order by StepNumber) 
    - Row_Number() over(partition by status order by StepNumber) grp
  from t
)
select Row_Number() over(order by grp) [Order],
Status, Count(*) [consecutive counts]
from g
group by Status, grp
user_153326500
Related