Thorsten Kettner Thorsten Kettner - 2 months ago 6
SQL Question

How to combine consecutive number ranges

I am looking for a neat way to combine consecutive number ranges in a single select statement.

Say my table has these records:

first_number last_number
0 9
10 19
20 29
40 49
50 59
70 79


Then output shall be as follows:

first_number last_number
0 29
40 59
70 79


This is what I've come up with:

select first_number, last_number_of_range
from
(
select
first_number, is_continuing, is_continued,
nvl(lead (last_number,1,null) over (order by first_number), last_number) as last_number_of_range
from
(
select *
from
(
select first_number, last_number,
case when lag (last_number,1,null) over (order by first_number) + 1 = first_number then 1 else 0 end as is_continuing,
case when lead (first_number,1,null) over (order by last_number) - 1 = last_number then 1 else 0 end as is_continued
from
(
select 0 as first_number, 9 as last_number from dual
union all
select 10 as first_number, 19 as last_number from dual
union all
select 20 as first_number, 29 as last_number from dual
union all
select 40 as first_number, 49 as last_number from dual
union all
select 50 as first_number, 59 as last_number from dual
union all
select 70 as first_number, 79 as last_number from dual
)
)
where is_continuing = 0 or is_continued = 0 -- remove all but first and last of consecutive records
)
)
where is_continuing = 0 -- now at last remove those records that gave us the last_number_of_range
;


This works fine. Only, it looks so complicated for so small a task. I would be interested to know wether there is a much more straight way than mine.

Answer

Please try:

with T1 as (
  select 
    row_number() over (order by first_number) RNum, 
    first_number, 
    last_number 
  From yourtable
)
,T (RNUM, first_number, last_number, CNT) as (
    select T1.*, 1 CNT from T1 where RNum=1
    union all
    SELECT b.RNUM, b.first_number, b.last_number, (case when b.first_number=T.last_number+1 then t.CNT 
                        else T.CNT+1 end) CNT
    from T1 b INNER JOIN T on b.RNum=T.RNum+1
)
select 
   min(first_number) as first_number,
    max(last_number) as last_number
From T group by T.CNT

SQL fiddle demo