Thorsten Kettner - 8 months ago 27

SQL Question

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
```