1

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
Dale K
  • 25,246
  • 15
  • 42
  • 71
markSS
  • 73
  • 6

1 Answers1

0

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
Stu
  • 30,392
  • 6
  • 14
  • 33
  • Note this *assumes* `StepNumber` will always be incremental; if it's something like an `IDENTITY` that is a poor assumption. – Thom A Dec 05 '21 at 21:54
  • True that was an assumption - it's just as easy to use a separate row_number I'll amend the query. – Stu Dec 05 '21 at 21:57