Joy Walker Joy Walker - 4 months ago 8
SQL Question

How to add sequence number to group of rows based on status column

I have a group of rows in order. Column "Status" has only two value 0/1. Now, I'd like to add a sequence number / group number for each 0/1 set. there can be 1 to many rows of 0's but only one 1 for each set in the end. How do I add a new column as sequence number that only increases when there is a 1.

example:

ID Status Row Group Number
1 0 1
2 0 1
3 1 1
4 0 2
5 1 2
6 0 3
7 0 3
8 0 3
9 1 3


question is how do I get the third column?

thank you.

Answer

Hmmm . . . This is a cumulative sum up to the previous row (plus 1). So, in SQL Server 2012+, you can do:

select t.*,
       1 + sum(status) over (order by id) - status as rowgroupnumber
from t;