CODEWITHSUNDEEP
sqlsql-server
Black
Black

Reputation: 4654

SQL Server: Increment row value depending on previous row

I have a table with the columns id and value. I'd like to create a column that groups the id. If a row's current value equals 0 then a new group in ideal_group will be created.

Table:

id | value | ideal_group
1    1       1
2    1       1
3    1       1
4    0       2
5    1       2
6    0       3
7    0       4

I'm thinking the solution should be something like:

SET @n = 1;
SELECT id, 
       CASE 
            WHEN value = 0 THEN @n = @n + 1 
       ELSE @n END AS ideal_group

But I'd prefer not to use an counter variable. Is there another way to go about this?

Upvotes: 2

Views: 2973

Answers (3)

McNets
McNets

Reputation: 10827

create table tbl (id int, value int);
insert into tbl values
(1, 1),
(2, 1),
(3, 1),
(4, 0),
(5, 1),
(6, 0),
(7, 0);
GO

7 rows affected

select id,
       value,
       1 + sum(iif(value = 0, 1, 0)) over 
                (order by id rows between unbounded preceding and current row) as ideal_group
from   tbl
GO

id | value | ideal_group
-: | ----: | ----------:
 1 |     1 |           1
 2 |     1 |           1
 3 |     1 |           1
 4 |     0 |           2
 5 |     1 |           2
 6 |     0 |           3
 7 |     0 |           4

dbfiddle here

Upvotes: 2

paparazzo
paparazzo

Reputation: 45106

If you reversed the 1 and 0 and it was only 1 or 0 this would be easier.

declare @T table (id int primary key, val int);
insert into @T values 
       (1, 1)
     , (2, 1)
     , (3, 1)
     , (4, 0)
     , (5, 1)
     , (6, 0)
     , (7, 0);
select t.id, t.val 
     , case when t.val = 0 then 1 else 0 end as trig
     , sum(case when t.val = 0 then 1 else 0 end) over (order by t.id) + 1 as grp
from @T t 
order by t.id; 

id          val         trig        grp
----------- ----------- ----------- -----------
1           1           0           1
2           1           0           1
3           1           0           1
4           0           1           2
5           1           0           2
6           0           1           3
7           0           1           4

Upvotes: 0

Michał Turczyn
Michał Turczyn

Reputation: 37525

Try the below code, I assumed, that values in value column are only 1s and 0s:

select id,
       value,
       sum(1 - value) over (order by id rows between unbounded preceding and current row) + 1 [ideal_group]
from MY_TABLE

More general solution (without mentioned assumption):

select id,
       value,
       sum(case value when 0 then 1 else 0 end) over (order by id rows between unbounded preceding and current row) + 1 [ideal_group]
from MY_TABLE

Upvotes: 4

Related Questions