Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,601 questions
This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
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:
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 <= q.id and prev = 0) as mx
from Q1 as q
),
Q3 as
(
select *, (select count(id) from Q2 where id <= q.id and mx = q.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.
taken from my question at stackoverflow.com, 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;