title: SQL Server GROUP BY COUNT Consecutive Rows Only
body: <p>I have a table called DATA on Microsoft SQL Server 2008 R2 with three non-nullable integer fields: ID, Sequence, and Value. Sequence values with the same ID will be consecutive, but can start with any value. I need a query that will return a count of consecutive rows with the same ID and Value.</p>
  
  <p>For example, let's say I have the following data:</p>
  
  <pre><code>ID  Sequence  Value
  --  --------  -----
  1         1      1
  5         1    100
  5         2    200
  5         3    200
  5         4    100
  10       10     10
  </code></pre>
  
  <p>I want the following result:</p>
  
  <pre><code>ID  Start  Value  Count
  --  -----  -----  -----
  1      1      1      1
  5      1    100      1
  5      2    200      2
  5      4    100      1
  10    10     10      1
  </code></pre>
  
  <p>I tried</p>
  
  <pre><code>SELECT ID, MIN([Sequence]) AS Start, Value, COUNT(*) AS [Count]
   FROM DATA
   GROUP BY ID, Value
   ORDER BY ID, Start
  </code></pre>
  
  <p>but that gives</p>
  
  <pre><code>ID  Start  Value  Count
  --  -----  -----  -----
  1      1      1      1
  5      1    100      2
  5      2    200      2
  10    10     10      1
  </code></pre>
  
  <p>which groups all rows with the same values, not just consecutive rows.</p>
  
  <p>Any ideas? From what I've seen, I believe I have to left join the table with itself on consecutive rows using ROW_NUMBER(), but I am not sure exactly how to get counts from that.</p>
  
  <p>Thanks in advance.</p>