Query non-consecutive NO numbers in Oracle

1. Create a table and insert data
SQL code
Create table lag_use (no number );
Insert into lag_use values (1 );
Insert into lag_use values (2 );
Insert into lag_use values (3 );
Insert into lag_use values (5 );
Insert into lag_use values (6 );
Insert into lag_use values (8 );
Insert into lag_use values (12 );
Insert into lag_use values (13 );
Insert into lag_use values (25 );
Insert into lag_use values (36 );

2. query nonconsecutive NO numbers.
 
SQL code
SELECT * FROM
(Select lag (NO, 1) OVER (order by no) + 1 AS START_NO, NO-1
FROM LAG_USE) NB
Where nb. NO <> NB. START_NO;
 
Or
SQL code
With tmp (
Select rownum r from dual connect by level <= (select max (no) from lag_use ))
Select min (r), max (r) from (
Select no, r, sum (decode (no, null, 0, 1) over (order by r) v
From tmp a, lag_use B
Where a. r = B. no (+ ))
Where no is null
Group by v;

Author: maziheng"