CODEWITHSUNDEEP
sqloracle-database
Thorsten Kettner
Thorsten Kettner

Reputation: 94859

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.

Upvotes: 5

Views: 1671

Answers (3)

Felypp Oliveira
Felypp Oliveira

Reputation: 2187

An old thread, but I have another solution... worked well in my tests and it seems very simple.

Uncommented one:

SELECT 
  MAX(r2.first_number) AS first_number, 
  r1.last_number 
FROM <table> AS r1
INNER JOIN <table> AS r2 ON 1=1
  AND r2.first_number < r1.last_number
LEFT JOIN <table> AS r3 ON 1=1
  AND r3.last_number  >= r1.last_number + 1
  AND r3.first_number <= r1.last_number + 1
LEFT JOIN <table> AS r4 ON 1=1
  AND r4.last_number  >= r2.first_number - 1
  AND r4.first_number <= r2.first_number - 1
WHERE 1=1
  and r3.first_number IS NULL
  and r4.first_number IS NULL
GROUP BY r1.last_number
;

Commented one:

SELECT 
  -- getting a first_number 
  -- lesser than the 
  -- maximum first_number available
  -- makes this maximum one
  -- an overlap...
  MAX(r2.first_number) first_number, 
  r1.last_number 
FROM <table> r1
INNER JOIN <table> r2 ON 1=1
  -- join the data so 
  -- for  a last_number  in r1 you can
  -- find a first_number in r2 that is
  -- lesser than its value, as 
  -- no first_number should be
  -- greater than (or equals?) a last_number
  AND r2.first_number < r1.last_number
LEFT JOIN <table> r3 ON 1=1
  -- matches when another range 
  -- overlaps last_number from r1
  AND r3.last_number  >= r1.last_number + 1
  AND r3.first_number <= r1.last_number + 1
LEFT JOIN <table> r4 ON 1=1
  -- matches when another range 
  -- overlaps first_number from r2
  AND r4.last_number  >= r2.first_number - 1
  AND r4.first_number <= r2.first_number - 1
WHERE 1=1
  -- get rows 
  -- with no overlaps on last_number and
  -- with no overlaps on first_number
  and r3.first_number IS NULL
  and r4.first_number IS NULL
GROUP BY r1.last_number
;

Upvotes: 0

Nick Krasnov
Nick Krasnov

Reputation: 27251

Here is another approach, which will give you desired output.

select min(first_number)  as first_number
     , max(last_number)   as last_number
 from ( 
        select first_number
              , last_number
              , sum(grp) over(order by first_number) as grp
           from ( select first_number
                       , last_number
                       , case
                           when first_number <>
                                   lag(last_number) 
                                     over(order by first_number) + 1
                           then 1
                           else 0
                         end as grp
                     from t1 )
       )
group by grp
order by 1

Result:

FIRST_NUMBER LAST_NUMBER
------------ -----------
           0          29
          40          59
          70          79

SQLFiddle demo

Upvotes: 3

TechDo
TechDo

Reputation: 18629

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

Upvotes: 2

Related Questions