grupo sql por solo filas que están en secuencia

Digamos que tengo la siguiente tabla:

MyTable --------- | 1 | A | | 2 | A | | 3 | A | | 4 | B | | 5 | B | | 6 | B | | 7 | A | | 8 | A | --------- 

Necesito la consulta SQL para dar salida a lo siguiente:

 --------- | 3 | A | | 3 | B | | 2 | A | --------- 

Básicamente estoy haciendo un group by pero solo para las filas que están juntas en la secuencia. ¿Algunas ideas?

Tenga en count que la database se encuentra en el server sql 2008. Hay una publicación sobre este tema, sin embargo, utiliza la function lag de oracle ().

Esto se conoce como el problema de "islas". Usando el enfoque de Itzik Ben Gan:

 ;WITH YourTable AS ( SELECT 1 AS N, 'A' AS C UNION ALL SELECT 2 AS N, 'A' AS C UNION ALL SELECT 3 AS N, 'A' AS C UNION ALL SELECT 4 AS N, 'B' AS C UNION ALL SELECT 5 AS N, 'B' AS C UNION ALL SELECT 6 AS N, 'B' AS C UNION ALL SELECT 7 AS N, 'A' AS C UNION ALL SELECT 8 AS N, 'A' AS C ), T AS (SELECT N, C, DENSE_RANK() OVER (ORDER BY N) - DENSE_RANK() OVER (PARTITION BY C ORDER BY N) AS Grp FROM YourTable) SELECT COUNT(*), C FROM T GROUP BY C, Grp ORDER BY MIN(N) 

esto funcionará para ti …

 SELECT Total=COUNT(*), C FROM ( SELECT NGroup = ROW_NUMBER() OVER (ORDER BY N) - ROW_NUMBER() OVER (PARTITION BY C ORDER BY N), N, C FROM MyTable )RegroupedTable GROUP BY C,NGroup 

Solo por diversión, sin funciones específicas de SQL y NO suponiendo que la columna de Id. Aumente monótonamente:

 WITH starters(name, minid, maxid) AS ( SELECT a.name, MIN(a.id), MAX(a.id) FROM mytable a RIGHT JOIN mytable b ON (a.name <> b.name AND a.id < b.id) WHERE a.id IS NOT NULL GROUP BY a.name ), both(name, minid, maxid) AS ( SELECT name, minid, maxid FROM starters UNION ALL SELECT name, MIN(id), MAX(id) FROM mytable WHERE id > (SELECT MAX(maxid) from starters) GROUP BY name ) SELECT COUNT(*), m.name, minid FROM both INNER JOIN mytable m ON id BETWEEN minid AND maxid GROUP BY m.name, minid 

Resultado (ignore la columna midid):

 (No column name) name minid 3 A 1 3 B 4 2 A 7