Retrive missing record - 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)

Retrive missing record

Author

Topic

imrul
Starting Member

36 Posts

Posted - 2007-09-05 : 08:26:39

Hi ,
I have a table with a column rowid( data type int), which store sequential record like 1,2,3,4.. My problem is that some record are missing. Say for example i have record like 1,2,4,5. in this sequence 3 is missing. How can I retrieve the missing record from a sequential record

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-05 : 08:35:00

See http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=55210
There are many links to efficient methods.

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-05 : 08:39:37

[code]declare @sample table (seq int)

insert @sample
select 1 union all
select 2 union all
select 4 union all
select 5 union all
select 7

select s1.seq + 1 as missing
from @sample as s1
left join @sample as s2 on s2.seq = s1.seq + 1
where s2.seq is null
and s1.seq < (select max(seq) from @sample)[/code]

E 12°55'05.25"
N 56°04'39.16"

imrul
Starting Member

36 Posts

Posted - 2007-09-05 : 11:14:53

Peso,
Thank you very much

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources