query to find gaps between numbers in column of a table
to find gaps requires a few things:
-
you need to bunch rows into related groups (there may be only 1 group in the simplest case)
-
you need to order rows in each group (usually by the column you want to find gaps in)
-
you need to know how to determine the “next” value in a sequence (in other words, given some number in your column what should be the next number (in the simplest case this of a sequential number the formula is #+1)).
With this information you can use various techniques depending upon what you want to know. If you problem is finding numbers with gaps in a sequential number that increments by 1 then you can do something like this. It will show you each row, where the next row does not provide the next value in the simple sequence, along with the PK of the two participating rows. From there you can compute whatever metrics make sense for you.
select *
from (
select group_key,pk,target_column
,lead(target_column) over (partition by group_key order by target_column) next_target_column
,lead(pk) over (partition by group_key order by target_column) next_pk
from
)
where next_target_column != target_column + 1
/
SQL> select * from t order by x;
X
----------
1
2
5
6
7
10
12
13
SELECT ROWNUM FROM dual CONNECT BY LEVEL <= xxx MINUS SELECT col_of_numbers FROM your_table;
where xxx is max numbers that are in your_table table. In this case 13. result is:
ROWNUM
----------
3
4
8
9
SQL>