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

by

In SQL Server 2012 and later, you have the luxury of 1) analytic functions, and 2) running totals:

declare @t table (
    Id int primary key,
    Value int not null
);

insert into @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 then 0 else 1 end) over(order by sq.Id) as [Counter]
from (
    select t.Id, t.Value, lag(t.Value, 1, null) over(order by t.Id) as [pVal]
    from @t t
) sq
order by sq.Id;

Also, this solution does not depend on ID column having no gaps.

More Related Contents:

  1. SQL Server – error when creating stored procedure
  2. How can foreign key constraints be temporarily disabled using T-SQL?
  3. Insert multiple rows WITHOUT repeating the “INSERT INTO …” part of the statement?
  4. How can I escape square brackets in a LIKE clause?
  5. Does T-SQL have an aggregate function to concatenate strings? [duplicate]
  6. Parameter Sniffing (or Spoofing) in SQL Server
  7. SQL Server String or binary data would be truncated
  8. How to change identity column values programmatically?
  9. SQL Server Regular expressions in T-SQL
  10. Maximum size of a varchar(max) variable
  11. best way to convert and validate a date string
  12. Concatenating Column Values into a Comma-Separated List
  13. Is there a way to simplify a NULL compare of 2 values
  14. TSQL Try / Catch within Transaction or vice versa?
  15. What is the maximum number of characters that nvarchar(MAX) will hold?
  16. TSQL: Create a view that accesses multiple databases
  17. Sequence as default value for a column
  18. Aggregate bitwise-OR in a subquery
  19. How do I flush the PRINT buffer in TSQL?
  20. OR Operator Short-circuit in SQL Server
  21. SQL Server 2008 – How do i return a User-Defined Table Type from a Table-Valued Function?
  22. Check if a string contains a substring in SQL Server 2005, using a stored procedure
  23. How to change default language for SQL Server?
  24. Cannot bulk load. Operating system error code 5 (Access is denied.)
  25. comparing a column to a list of values in t-sql
  26. Does query plan optimizer works well with joined/filtered table-valued functions?
  27. Random record from a database table (T-SQL)
  28. Random Weighted Choice in T-SQL
  29. time format in SQL Server
  30. SQL Server stored procedure return code oddity

Leave a Comment