sql server - sql group by only rows which are in sequence -

say have following table:

mytable --------- | 1 | | | 2 | | | 3 | | | 4 | b | | 5 | b | | 6 | b | | 7 | | | 8 | | --------- 

i need sql query output following:

--------- | 3 | | | 3 | b | | 2 | | --------- 

basically i'm doing group by rows in sequence. ideas?

note database on sql server 2008. there post on topic uses oracle's lag() function.

this known "islands" problem. using itzik ben gan's approach:

;with yourtable ( select 1 n, 'a' c union select 2 n, 'a' c union select 3 n, 'a' c union select 4 n, 'b' c union select 5 n, 'b' c union select 6 n, 'b' c union select 7 n, 'a' c union select 8 n, 'a' c ),      t      (select n,                 c,                 dense_rank() on (order n) -                  dense_rank() on (partition c order n) grp            yourtable) select count(*),        c   t group  c,           grp  order min(n)