Find gaps in sequential numbering

I would like to find the missing numbers in a sequential increase in a
column
So for instance
Create table #test
(numcol int)
insert #test values(1)
insert #test values(2)
insert #test values(3)
insert #test values(4)
insert #test values(7)
insert #test values(8)
insert #test values(9)
insert #test values(11)
insert #test values(100)
Would be nice to get the range of missing values such as
Minvalue Maxvalue
5 6
10 10
12 99
ThanksHere is one solution:
SELECT minval + 1 AS MinValue,
maxval - 1 AS MaxValue
FROM
(SELECT numcol,
(SELECT MIN(numcol)
FROM #test AS T2
WHERE T2.numcol > T1.numcol)
FROM #test AS T1) AS T3(minval, maxval)
WHERE maxval - minval > 1
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||Hassan
select
min(i) as low,
max(i) as high
from (
select
N1.num,
count(N2.num) - N1.num
from Numbers as N1, Numbers as N2
where N2.num <= N1.num
group by N1.num
) as N(i,gp)
group by gp
"Hassan" <hassan@.hotmail.com> wrote in message
news:u5PlkdgXIHA.6140@.TK2MSFTNGP02.phx.gbl...
>I would like to find the missing numbers in a sequential increase in a
>column
> So for instance
> Create table #test
> (numcol int)
> insert #test values(1)
> insert #test values(2)
> insert #test values(3)
> insert #test values(4)
> insert #test values(7)
> insert #test values(8)
> insert #test values(9)
> insert #test values(11)
> insert #test values(100)
>
> Would be nice to get the range of missing values such as
> Minvalue Maxvalue
> 5 6
> 10 10
> 12 99
> Thanks
>