SQL Query to group continuous range of numbers into different grouping sets

Question

I've a table with approximately more than a million records. Few of the sample values are given below:

Group    MemberNo
ABC           100
ABC           101
ABC           200
ABC           201
ABC           202
ABC           203
XYZ           100
XYZ           101
ABC           204
XYZ           301
XYZ           302
ABC           500
ABC           600

I wish to group continuous range of values with same group into a sets like this:

Group  FromMemberNo      ToMemberNo
ABC             100             101
ABC             200             204
XYZ             100             101
XYZ             301             302
ABC             500             500
ABC             600             600

Please see from the above table that since 100 and 101 are continuous it has been grouped into one record ABC 100 to 101. I've tried this thread and is working fine for me. But it is taking quite a long time than desired.
Please help me to achieve this.

Thanks in advance.

Answer 1

another solution. I can figure about the perfs but it seems doing the job (sql 2012 only)

declare @t table (g varchar(3), mn int)

insert into @t values 
('ABC',           100),
('ABC',           101),
('ABC',           200),
('ABC',           201),
('ABC',           202),
('ABC',           203),
('XYZ',           100),
('XYZ',           101),
('ABC',           204),
('XYZ',           301),
('XYZ',           302),
('ABC',           500),
('ABC',           600),
('XYZ',           400);


with ctet as (
    select 
        row_number() over (order by g, mn) rn,
        *, 
        case when lag(mn, 1) over (order by g, mn) <> mn - 1 then 1 else 0 end as d 
    from 
        @t
)

select g, min(mn), max(mn)
from
    (
    select 
        *,
        (select sum(d) from ctet vv where vv.rn <= ctet.rn) s
    from 
        ctet
    ) v
group by g, s

I'm quite sure there is a smarter solution with lag or lead, but I can't find it.

===== EDIT =====

finally also works for 2005

 with ctet as (
    select 
        row_number() over (order by t.g, t.mn) rn,
        t.*, 
        case when tt.g is null then 1 else 0 end as d
    from 
        @t t
        left join @t tt on t.g = tt.g and t.mn = tt.mn + 1
)