Given the Sequence of numbers below, how can I assign a Sequence number so that each block which starts with a 1 and ends with a 0 is given a unique identifying number? How can I create the Sequence Number column using TSQL?
| Sequence | SequenceNumber |
|---|---|
| 1 | 1 |
| 1 | 1 |
| 0 | 1 |
| 1 | 2 |
| 1 | 2 |
| 1 | 2 |
| 0 | 2 |
| 1 | 3 |
| 0 | 3 |
CodePudding user response：
Use COUNT OVER to count zeros. You need some column to determine the order. I am calling it sortkey in the following query:
select
t.*,
count(case when sequence = 0 then 1 end)
over (order by sortkey
rows between unbounded preceding and 1 preceding) 1
as sequence_number
from mytable t
order by sortkey;