with testdata as
(
select 'A' name, 1 num from dual union all
select 'B', 4 from dual union all
select 'C', 8 from dual union all
select 'D', 18 from dual union all
select 'h', 21 from dual union all
select 'E', 34 from dual union all
select 'f', 35 from dual union all
select 't', 36 from dual union all
select 'L', 51 from dual union all
select 'Z', 55 from dual
)
select name,num
from
(
select name,num,lag(num,1,0) over (partition by 1 order by 1) prev_num from testdata
order by num
)
where num >= (prev_num+15)
select num
from (select trunc(num/15), min(num)
from test
group by trunc(num/15)
)
SQL> edit
Wrote file sqlplus_buffer.sql
1 WITH test AS
2 (
3 SELECT 'A' AS name, 1 AS num FROM DUAL UNION ALL
4 SELECT 'B' AS name, 4 AS num FROM DUAL UNION ALL
5 SELECT 'C' AS name, 8 AS num FROM DUAL UNION ALL
6 SELECT 'D' AS name, 18 AS num FROM DUAL UNION ALL
7 SELECT 'h' AS name, 21 AS num FROM DUAL UNION ALL
8 SELECT 'E' AS name, 34 AS num FROM DUAL UNION ALL
9 SELECT 'f' AS name, 35 AS num FROM DUAL UNION ALL
10 SELECT 't' AS name, 36 AS num FROM DUAL UNION ALL
11 SELECT 'L' AS name, 51 AS num FROM DUAL UNION ALL
12 SELECT 'Z' AS name, 55 AS num FROM DUAL
13 )
14 SELECT a.name,a.num,a.diff
15 FROM
16 (SELECT name,num,num-(LAG(num,1) OVER (order by num)) AS diff FROM test order by num) a
17* WHERE a.diff >= 15 OR a.diff IS NULL
SQL> /
N NUM DIFF
- ---------- ----------
A 1
L 51 15
SQL>
I hope this helps. I had a hard time understanding your requirements. with test as (
select 'A' nam, 14 num from dual union all
select 'B', 15 from dual union all
select 'C', 19 from dual union all
select 'D', 29 from dual union all
select 'E', 30 from dual union all
select 'F', 50 from dual union all
select 'G', 51 from dual)
select num
from (select trunc((num-minnum)/15), min(num) num
from test, (select min(num) minnum from test) x
group by trunc((num-minnum)/15)
)
NU
--
14
29
50