Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> 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