Count consecutive duplicate values in SQL

by

I am going to presume that id is unique and increasing. You can get counts of consecutive values by using the different of row numbers. The following counts all sequences:

select grp, value, min(id), max(id), count(*) as cnt
from (select t.*,
             (row_number() over (order by id) - row_number() over (partition by value order by id)
             ) as grp
      from table t
     ) t
group by grp, value;

If you want the longest sequence of 0s:

select top 1 grp, value, min(id), max(id), count(*) as cnt
from (select t.*,
             (row_number() over (order by id) - row_number() over (partition by value order by id)
             ) as grp
      from table t
     ) t
group by grp, value
having value = 0
order by count(*) desc

More Related Contents:

  1. Difference between CTE and SubQuery?
  2. Update records in table from CTE
  3. Common Table Expression, why semicolon?
  4. When to use Common Table Expression (CTE)
  5. Is there a performance difference between CTE , Sub-Query, Temporary Table or Table Variable?
  6. CTE error: “Types don’t match between the anchor and the recursive part”
  7. Incorrect syntax near the keyword ‘with’…previous statement must be terminated with a semicolon
  8. How to create Temp table with SELECT * INTO tempTable FROM CTE Query
  9. Select statement to return parent and infinite children
  10. Use one CTE many times
  11. The multi-part identifier could not be bound
  12. Subquery using Exists 1 or Exists *
  13. How can I query a value in SQL Server XML column
  14. Most efficient T-SQL way to pad a varchar on the left to a certain length?
  15. SQL – How to store and navigate hierarchies?
  16. SQL Query to get aggregated result in comma separators along with group by column in SQL Server
  17. how can I Update top 100 records in sql server
  18. How to insert multiple records and get the identity value?
  19. How can a LEFT OUTER JOIN return more records than exist in the left table?
  20. Count Number of Consecutive Occurrence of values in Table
  21. How to replace (null) values with 0 output in PIVOT
  22. Is there a SELECT … INTO OUTFILE equivalent in SQL Server Management Studio?
  23. How can I compare time in SQL Server?
  24. SQL – Difference between COALESCE and ISNULL? [duplicate]
  25. Improve SQL Server query performance on large tables
  26. T-SQL Skip Take Stored Procedure
  27. Return rows in the exact order they were inserted
  28. Check if table exists and if it doesn’t exist, create it in SQL Server 2008
  29. MAX vs Top 1 – which is better?
  30. Insert into from CTE

Leave a Comment