SQL Server GROUP BY COUNT Sequential Rows Only

Question

I have a table called DATA on Microsoft SQL Server 2008 R2 with three fields with zero value: ID, Sequence and Value. Sequence values ​​with the same identifier will be sequential, but can begin with any value. I need a query that will return the number of consecutive lines with the same identifiers and value.

For example, let's say I have the following data:

ID Sequence Value -- -------- ----- 1 1 1 5 1 100 5 2 200 5 3 200 5 4 100 10 10 10 

I want to get the following result:

 ID Start Value Count -- ----- ----- ----- 1 1 1 1 5 1 100 1 5 2 200 2 5 4 100 1 10 10 10 1 

I tried

 SELECT ID, MIN([Sequence]) AS Start, Value, COUNT(*) AS [Count] FROM DATA GROUP BY ID, Value ORDER BY ID, Start 

but at the same time

 ID Start Value Count -- ----- ----- ----- 1 1 1 1 5 1 100 2 5 2 200 2 10 10 10 1 

which groups all lines with the same values, not just consecutive lines.

Any ideas? From what I saw, I believe that I need to leave to join the table with me in sequential rows using ROW_NUMBER (), but I do not know exactly how to get an account from this.

Thanks in advance.

Answer 1

You can use Sequence - ROW_NUMBER() OVER (ORDER BY ID, Val, Sequence) AS g to create a group:

 SELECT ID, MIN(Sequence) AS Sequence, Val, COUNT(*) AS cnt FROM ( SELECT ID, Sequence, Sequence - ROW_NUMBER() OVER (ORDER BY ID, Val, Sequence) AS g, Val FROM yourtable ) AS s GROUP BY ID, Val, g 

Please see the fiddle here .