I have also had to do something similar
Jeremy, March 03, 2003 - 5:46 pm UTC
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
Great Query
Logan Palanisamy, March 04, 2003 - 2:01 pm UTC
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
Case When
Jeremy, March 04, 2003 - 8:32 pm UTC
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 :).
very cool query
noinim, March 28, 2003 - 5:22 pm UTC
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.