Search code examples
sqloracle-databasegroupinggaps-and-islands

Oracle SQL Grouping In Ranges

I am looking for ideas on how to group numbers into low and high ranges in Oracle SQL. I looking to to avoid cursors...any ideas welcome

Example input

ID LOW HIGH
A 0 2
A 2 3
A 3 5
A 9 11
A 11 13
A 13 15
B 0 1
B 1 4
B 7 9
B 11 12
B 12 17
B 17 18

Which would result in the following grouping into ranges

ID LOW HIGH
A 0 5
A 9 15
B 0 4
B 7 9
B 11 18
Solution

  • This is a Gaps & Islands problem. You can use the traditional solution.

    For example:

    select max(id) as id, min(low) as low, max(high) as high
from (
  select x.*, sum(i) over(order by id, low) as g
  from (
    select t.*,
      case when low = lag(high) over(partition by id order by low) 
            and id = lag(id) over(partition by id order by low) 
           then 0 else 1 end as i
    from t
  ) x 
) y
group by g

    Result:

     ID  LOW  HIGH 
 --- ---- ---- 
 A   0    5    
 A   9    15   
 B   0    4    
 B   7    9    
 B   11   18

    See running example at db<>fiddle.