create table t (val number not null);
insert into t values (1 );
insert into t values (2 );
insert into t values (3 );
insert into t values (4 );
insert into t values (8 );
insert into t values (9 );
insert into t values (10);
insert into t values (11);
insert into t values (12);
insert into t values (13);
insert into t values (20);
insert into t values (21);
insert into t values (22);
insert into t values (23);
insert into t values (24);
commit;
with lData as (
select val,
lag(val, 1) over (order by val) as lag_val,
lead(val, 1) over (order by val) as lead_val
from t),
lDataBound as (
select val,
lag_val,
case when (lag_val is null) or (val != lag_val + 1) then 'Y'
else 'N' end as lower_bound,
case when (lead_val is null) or (val != lead_val - 1) then 'Y'
else 'N' end as upper_bound
from lData),
lData_upper_bound as (
select val,
row_number() over (order by val) as rn
from lDataBound
where upper_bound = 'Y'),
lData_lower_bound as (
select val,
row_number() over (order by val) as rn
from lDataBound
where lower_bound = 'Y')
select a.val as lower_bound,
b.val as upper_bound
from lData_lower_bound a
join lData_upper_bound b
on b.rn = a.rn
order by a.rn;
LOWER_BOUND UPPER_BOUND
----------- -----------
1 4
8 13
20 24
Regards
Benoit
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google
Groups "Oracle PL/SQL" group.
To post to this group, send email to Oracle-PLSQL@googlegroups.com
To unsubscribe from this group, send email to
oracle-plsql-unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/Oracle-PLSQL?hl=en
-~----------~----~----~----~------~----~------~--~---
