Find missing sequence number - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Find missing sequence number

Author

Topic

jonjsilver2
Starting Member

14 Posts

Posted - 2010-12-16 : 12:17:33

I have a table with a sequencial number column. That is this table has rows with the values 1,2,3,...10000
However a couple of the rows have been deleted, so in the table I have
1,2,3,5,6,7,9,....

Any idea how I do a select to find the missing sequence numbers - or at least the first missing sequence number?

thanks,
jon

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-12-16 : 13:38:11

http://www.sqlservercentral.com/articles/SQL+Puzzles/findinggapsinasequentialnumbersequence/2336/

No, you're never too old to Yak'n'Roll if you're too young to die.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-12-16 : 15:34:57

[code]declare @sample table (i int)
insert @sample select 1 union all select 2 union all select 3 union all select 5 union all select 6 union all select 7 union all select 9

select top(1) s1.i + 1
from @sample AS s1
left join @sample as s2 ON s2.i = s1.i + 1
where s2.i is null
order by s1.i[/code]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources