Find consecutive numbers in groups in SQL

Question

I need to find consecutive numbers in groups in SQL.

Example: Table1

   id  | group   | result (group id)
-------+---------+-----------------------------
    1  |   a     |  1
    2  |   a     |  1
    3  |   a     |  1
    4  |   a     |  1
    5  |   a     |  1
    8  |   a     |  2
   10  |   a     |  3
   13  |   a     |  4
   14  |   a     |  4
   15  |   a     |  4
   16  |   a     |  4
   20  |   a     |  5
    2  |   b     |  1
    3  |   b     |  1
    4  |   b     |  1
    5  |   b     |  1
    8  |   b     |  2
   10  |   b     |  3
   13  |   b     |  4
   16  |   b     |  5
   20  |   b     |  6

I found this solution ( Group by numbers that are in sequence ) but I can not keep it for groups

WITH firstrows AS
 (SELECT id, ROW_NUMBER() OVER (ORDER BY id) groupid
  FROM Table1 a
  WHERE id - 1 NOT IN (SELECT b.id FROM Table1 b)
 )
SELECT id, 
    (SELECT MAX(b.groupid)
     FROM firstrows b
     WHERE b.id <= a.id
    ) groupid
FROM Table1 a 

Answer 1

There is an alternative solution using lag() window function:

select id, grp, sum(mark) over (partition by grp order by id)
from (
  select 
    id, grp, 
    case when id - 1 = lag(id) over (partition by grp order by id) then 0 else 1 end as mark
  from my_table
  ) s;

Test it in SqlFiddle.

Answer 2

Perhaps another option

Example

Select id
      ,[group]
      ,result = dense_rank() over (Partition by [group] order by grp)
 From ( 
       Select *
             ,Grp = id - row_number() over (Partition by [group] order by id)
        From  YourTable
       ) A
 Order by [group],id

Returns

id  group   result
1   a       1
2   a       1
3   a       1
4   a       1
5   a       1
8   a       2
10  a       3
13  a       4
14  a       4
15  a       4
16  a       4
20  a       5
2   b       1
3   b       1
4   b       1
5   b       1
8   b       2
10  b       3
13  b       4
16  b       5
20  b       6