Make geometric sequence from series of bit values

Marek Brojo 21 Reputation points

I have this table:

declare @Table table (value int)  
insert @Table select 0  
insert @Table select 1  
insert @Table select 1  
insert @Table select 1  
insert @Table select 0  
insert @Table select 1  
insert @Table select 1  

Now, I need to make a Select query, which would add a column. This column will make a geometric sequence once there is a serie of value 1 in column value.

This would be the result:

A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,551 questions
0 comments No comments
{count} votes

Accepted answer
  1. Viorel 111.8K Reputation points

    I think that you need a column that specifies the order of these values, otherwise the order is undefined. The next possible approach uses an additional id column:

    declare @Table table (id int primary key, value int)
    insert @Table select 1, 0
    insert @Table select 3, 1
    insert @Table select 4, 1
    insert @Table select 7, 1
    insert @Table select 8, 0
    insert @Table select 14, 1
    insert @Table select 17, 1
    with Q1 as
        select *, isnull(lag(value) over (order by id), 0) as prev
        from @Table
    Q2 as
        select *, (select max(id) from Q1 where id <= and prev = 0) as mx
        from Q1 as q
    Q3 as
        select *, (select count(id) from Q2 where id <= and mx = as c
        from Q2 as q
    select value, case value when 1 then POWER(2, c) else 0 end as GeometricSequence
    from Q3
    order by id

    There are alternatives, though. Maybe show your attempts too.

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. Marek Brojo 21 Reputation points

    taken from my question at, solution by Gordon Linoff

    select t.*,
           value * power(2, row_number() over (partition by grp order by id) - 1) as generatedsequence
    from (select t.*, sum(1 - value) over (order by id) as grp
          from @table t
         ) t;
    0 comments No comments