sql server - T-SQL incrementing counter based on change in a column value -


i have sample table below. table sorted column not shown. need increment counter column value based on whenever there change in value column. please see below example. how can done in t-sql (sql server 2014).

id value 1  3 2  3 3  2 4  2 5  2 6  3 7  0 8  0 

expected output:

id value counter 1  3     1 2  3     1 3  2     2 4  2     2 5  2     2 6  3     3 7  0     4 8  0     4 

in sql server 2012 , later, have luxury of 1) analytic functions, , 2) running totals:

declare @t table (     id int primary key,     value int not null );  insert @t (id, value) values (1, 3), (2, 3), (3, 2), (4, 2), (5, 2), (6, 3), (7, 0), (8, 0);  select sq.id, sq.value,     sum(case when sq.pval = sq.value 0 else 1 end) over(order sq.id) [counter] (     select t.id, t.value, lag(t.value, 1, null) over(order t.id) [pval]     @t t ) sq order sq.id; 

also, solution not depend on id column having no gaps.


Comments

Popular posts from this blog

Payment information shows nothing in one page checkout page magento -

tcpdump - How to check if server received packet (acknowledged) -