Friday, December 2, 2011

Finding Missing Numbers in SQL Server

Finding Missing/Skipped Numbers in table  in SQL Server  


Declare @tbl table(slno int)

insert into @tbl(slno) values(1)
insert into @tbl(slno) values(3)
insert into @tbl(slno) values(4)
insert into @tbl(slno) values(5)
insert into @tbl(slno) values(11)
insert into @tbl(slno) values(13)
insert into @tbl(slno) values(18)
insert into @tbl(slno) values(8008)

select * from @tbl

select c.slno from
(select a.slno,(select b.slno from (select ROW_NUMBER() OVER(ORDER BY Slno) as Actslno, * from @tbl) b where b.ActSlNo=a.ActSlNo+1) as nextSlno from (select ROW_NUMBER() OVER(ORDER BY Slno) as Actslno, * from @tbl) a) c
where ISNULL(c.nextSlno,0)-ISNULL(c.slno,0)>1

No comments:

Post a Comment