I also had to support an application where I've needed to find gaps of ids to insert records into that couldn't just be appended to the end. Here is a query that I use to find the top five biggest gaps to insert recs into.

select * from (
select empno, one_before, Diff, dense_rank() over (order by Diff desc) rank from (
select empno, one_before,
case when (empno - one_before) > 1 then (empno - one_before)
else 1
end Diff
from (
select empno, lag(empno) over(order by empno) one_before
from emp order by empno))
)
where rank <= 5

Out put like:

EMPNO ONE_BEFORE DIFF RANK
---------- ---------- ---------- ----------
212 101 111 1
99 55 44 2
54 36 18 3
30 19 11 4
10 3 7 5
19 12 7 5

Jeremy,

Certainly a great query. How about eliminating "case when .." and making it even simpler.

select * from (
select empno, one_before, Diff, dense_rank() over (order by Diff desc) rank from
(
select empno, one_before,(empno - one_before) Diff
from (
select empno, lag(empno) over(order by empno) one_before
from emp order by empno))
)
where rank <= 5


My two cents

The case when is for the first row (ID = 1). The first row when compared to the lag/previous row (which would evaluate to null) will always show up in your results unless you have the "case when" in there. So... thanks for your two cents, but the "case when" does have a purpose which would be noticable if you actually try the query out :).

I find myself thinking that if you are counting the number of empty slots, gapsize is overstated by one. If on the other hand you are counting say dollars, the gapsize is correct as a difference in dollars.

That is a very cool query.