2

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.

Community
  • 1
  • 1
Nagesh
  • 1,288
  • 3
  • 22
  • 46
  • 1
    The accepted answer in the question you link is probably the most efficient way of doing it. How long is it taking and how long are you expecting it to take? What does the execution plan look like? Do you have an index on `Group,MemberNo`? – Martin Smith Jan 22 '13 at 12:45
  • Hi @MartinSmith, there are 4 more columns and for simplicity I've added only one column. I've indexes on all those columns but still the query is taking 4 to 5 seconds. – Nagesh Jan 22 '13 at 12:48
  • You need to process million rows every time? – Hamlet Hakobyan Jan 22 '13 at 12:50
  • Hi @HamletHakobyan, I'm planning to implement this as a view in Data Layer without touching my application logic. Your are true that it is not required to process all rows every time. – Nagesh Jan 22 '13 at 12:52
  • In you application, i think, you need to filter it by Group. Have you index on group? Try test with difference filter conditions by group. – Hamlet Hakobyan Jan 22 '13 at 12:56
  • 1
    Please post your actual table structure along with indexes, the query you are running and the execution plan and we can take a look and see if you are missing any indexes that would avoid a sort. – Martin Smith Jan 22 '13 at 13:14
  • possible duplicate of [SQL Query for Grouping the results based on sequence](http://stackoverflow.com/questions/5087864/sql-query-for-grouping-the-results-based-on-sequence) – Andriy M Jan 22 '13 at 18:39

1 Answers1

2

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
)
tschmit007
  • 7,559
  • 2
  • 35
  • 43