CODEWITHSUNDEEP
sqlt-sql
AInur_S
AInur_S

Reputation: 3

Can I start a new group when value changes from 0 to 1?

Can I somehow assign a new group to a row when a value in a column changes in T-SQL?

I would be grateful if you can provide solution that will work on unlimited repeating numbers without CTE and functions. I made a solution that work in sutuation with 100 consecutive identical numbers(with 

coalesce(lag()over(), lag() over(), lag() over() ) - it is too bulky

but can not make a solution for a case with unlimited number of consecutive identical numbers.

Data

id  somevalue  
1       0            
2       1       
3       1       
4       0       
5       0       
6       1       
7       1       
8       1       
9       0       
10      0       
11      1       
12      0       
13      1       
14      1       
15      0       
16      0

Expected 

id  somevalue  group
1       0       1             
2       1       2
3       1       2
4       0       3
5       0       3
6       1       4
7       1       4
8       1       4
9       0       5
10      0       5
11      1       6
12      0       7
13      1       8
14      1       8
15      0       9
16      0       9

Upvotes: 0

Views: 126

Answers (2)

MandyShaw
MandyShaw

Reputation: 1156

Here's a different approach:

First I created a view to provide the group increment on each row:

create view increments as 
select
n2.id,n2.somevalue,
case when n1.somevalue=n2.somevalue then 0 else 1 end as increment
from
  (select 0 as id,1 as somevalue union all select * from mytable) n1
  join mytable n2
  on n2.id = n1.id+1

Then I used this view to produce the group values as cumulative sums of the increments:

select id, somevalue,
(select sum(increment) from increments i1 where i1.id <= i2.id)
from increments i2

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269953

If you just want a group identifier, you can use:

select t.*,
       min(id) over (partition by some_value, seqnum - seqnum_1) as grp
from (select t.*,
             row_number() over (order by id) as seqnum,
             row_number() over (partition by somevalue order by id) as sequm_1
      from t
     ) t;

If you want them enumerated . . . well, you can enumerate the id above using dense_rank(). Or you can use lag() and a cumulative sum:

select t.*,
       sum(case when some_value = prev_sv then 0 else 1 end) over (order by id) as grp
from (select t.*,
             lag(somevalue) over (order by id) as prev_sv
      from t
     ) t;

Upvotes: 2

Related Questions