Very nice! While at first glance this seems complicated, it is easily broken down into discrete, fairly simple to understand, table transforms. I would be tempted to use this rather than writing it procedurally. Mike
On Mon, Sep 7, 2009 at 1:35 AM, benoit bordeaux <benoit.depla...@gmail.com>wrote: > > 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 -~----------~----~----~----~------~----~------~--~---
