Re: finding ranges and gaps in the range

Hi,

Analytics can solve this. Try this:
SQL> select d_no, min(val),max(val) from   2 (select d_no,val,max(cnt) over (partition by d_no order by val) grp
  3 from (
  4 select d_no,val,
  5 case when lag(val) over (partition by d_no order by val)<val-1 then
  6 row_number() over (partition by d_no order by val)   7 when row_number() over (partition by d_no order by val)=1 then 1   8 else NULL
  9 end cnt from t1))
 10 group by d_no,grp;

      D_NO MIN(VAL) MAX(VAL)
---------- ---------- ----------

         1         20         23
         1         25         25
         1       1503       1504
         3        502        504
         3        600        600

SQL> regards,

Arian Received on Tue Feb 28 2006 - 00:51:13 CST