sql group by only rows which are in sequence

gaps-and-islands, group-by, sql, sql-server, sql-server-2008

Solution

This is known as the "islands" problem. Using Itzik Ben Gan's approach:

;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)

Problem

Say I have the following table: ``` MyTable --------- | 1 | A | | 2 | A | | 3 | A | | 4 | B | | 5 | B | | 6 | B | | 7 | A | | 8 | A | --------- ``` I need the sql query to output the following: ``` --------- | 3 | A | | 3 | B | | 2 | A | --------- ``` Basically I'm doing a `group by` but only for rows which are together in the sequence. Any ideas? Note that the database is on sql server 2008. There is a post on this topic however it uses oracle's lag() function.

Original source