CODEWITHSUNDEEP
sqloracle-databaseoracle11g
Hrithu
Hrithu

Reputation: 281

How to break sequence

i have a table named sequence with a single column val and table looks like

val
-----
1
2
3
5
8
9
10

i need to identify the range of numbers if there is a break in sequence and output like

range_start    range_end
-----------    ----------
1              3
5              5
8              10

Upvotes: 0

Views: 135

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269933

Subtract an increasing sequence and you'll get a constant for values that are in sequence. Then aggregate:

select min(val), max(val)
from (select val, row_number() over (order by val) as seqnum
      from t
     ) t
group by (val - seqnum);

Upvotes: 2

The Impaler
The Impaler

Reputation: 48810

You can use the LAG() function to assign a group by number to each value. Then grouping is easy.

For example:

with
a as (
  select val,
    case when lag(val) over(order by val) = val - 1 then 0 else 1 end as adjacent
  from sequence
),
b as (
  select val,
  sum(adjacent) over(order by val) as grp
  from a
)
select min(val) as range_start, max(val) as range_end 
from b 
group by grp

Result:

RANGE_START  RANGE_END
-----------  ---------
1            3        
5            5        
8            10

For reference, the data script I used is:

create table sequence (val number(6));

insert into sequence (val) values (1);
insert into sequence (val) values (2);
insert into sequence (val) values (3);
insert into sequence (val) values (5);
insert into sequence (val) values (8);
insert into sequence (val) values (9);
insert into sequence (val) values (10);

Side Note: sequence is a really bad name for a table. Some tools may get confused by it. Sometimes you'll be forced to enclose it in double quotes all around the place.

Upvotes: 0

Related Questions