Skip to Main Content
  • Questions
  • Bring the consecutive number(strat_val,end_val) record based on id as one record and add new record if the consecutive number is breaks down

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Rajesh.

Asked: September 15, 2025 - 9:32 pm UTC

Last updated: September 16, 2025 - 2:51 pm UTC

Version: 19

You Asked

Bring the consecutive number(strat_val,end_val) record based on id as one record and add new record if the consecutive number is breaks down

https://livesql.oracle.com/next/?compressed_code=H4sIAAAAAAAACo3PwQqCQBCA4fvCvsPcVBjBXTWLboXHCoLOMupCgm2wO%252Fr8oXbo1jKn%252BflgmM4ZYgNM7WiAjeemX3YpYikAAIYeZnLdk5yOVZbgVj2T42amEa6Py6m%252Bf7Ox%252FRrBTq%252FWOCmSoxTbDNYbxzBYfv%252BcmWmcjI8hoggV5gv%252FDwvchcEKVRYmlUZVBtIdqkMY1RlqHUbzEvMqkO6xWN9K0%252Fp2%252FgB1bHIywAEAAA%253D%253D&code_language=PL_SQL&code_format=false

create table test_date 
(
    id varchar2(10),
    start_val NUMBER,
    end_val  number
);


input data :
insert into test_date values( 'a',1,3);
insert into test_date values( 'a',4,6);
insert into test_date values( 'a',7,10);
insert into test_date values( 'a',12,15);
insert into test_date values( 'a',16,19);
insert into test_date values( 'a',20,22);
insert into test_date values( 'a',35,37);
insert into test_date values( 'a',38,40);


output data:

'a' , 1, 10
'a' , 12, 19
'a' , 35 , 40

and Chris said...

There are several ways you can do this; here's how using SQL pattern matching:

select * from test_date
match_recognize ( 
  order by start_val
  measures
    id as id, 
    first ( start_val ) as strt, 
    last ( end_val ) as en
  pattern ( init cons* )
  define 
    cons as start_val = prev ( end_val ) + 1
);

ID               STRT         EN
---------- ---------- ----------
a                   1         10
a                  12         22
a                  35         40


(I think the second group should end with 22 rather than 19 given the test data)

You can find a detailed discussion of this technique at https://blogs.oracle.com/sql/post/how-to-calculate-the-longest-and-current-consecutive-streaks-with-oracle-sql
And see other approaches at https://livesql.oracle.com/ords/livesql/file/content_F8P1XASWD667NDOFJTM74NYE9.html

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.