Finding PK Gaps 2

[webmigit]

Is there a way, without modifying a table, to find gaps in the primary key... IE... where rows have been deleted, and to select the two rows surrounding it?

So In this table..

ID | Name
---------
1  | John
2  | Jim
4  | Suzi
5  | Donald
7  | Jeffrey
10 | Zach
11 | Kimmy
12 | Bobby

I would either get the row results
2, Jim; 4, Suzi
5, Donald; 7, Jeffrey
7, Jeffrey; 10, Zach

or

2. Jim
4. Suzi
5. Donald
7 Jeffrey
10 Zach

Thanks,
Tony

 

[bborissov]

--- Preparing Test data
declare @test table (id int, name varchar(50))
insert into @test values ( 1,'John')
insert into @test values ( 2,'Jim')
insert into @test values ( 4,'Suzi')
insert into @test values ( 5,'Donald')
insert into @test values ( 7,'Jeffrey')
insert into @test values (10,'Zach')
insert into @test values (11,'Kimmy')
insert into @test values (12,'Bobby')
--- End of preparing
-- Just change @Test and aliases with your table name

SELECT Test.*, 
       (SELECT MAX(Id) FROM @Test Test3 WHERE Test3.Id < Test.Id) AS LastId,
       (SELECT Name FROM @Test Test4 WHERE Test4.Id = (SELECT MAX(Id) FROM @Test Test3 WHERE Test3.Id < Test.Id)) AS LastName
FROM @Test Test
LEFT JOIN @Test Test1 ON (Test.Id-1) = Test1.Id
WHERE Test1.Id IS NULL 
--- if you want FIRST record to be excluded from resultser
--- just uncomments following line
---  AND Test.Id <> (SELECT MIN(Id) FROM @Test)
ORDER BY Test.Id

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.
MVP VFP

 

[TysonLPrice]

This was from a previous post here at Tek-Tips where that was asked. I don't remember who posted it so I can't give the credit to them. Just another way to skin a cat...

Create table #SequenceTable
( SeqNumber Int
)

Declare @Seq int

Set @Seq = 10

while @Seq <= 100
Begin
Insert into #SequenceTable values(@Seq)
Set @Seq = @Seq + 1
End

Insert into #SequenceTable values(101)
Insert into #SequenceTable values(102)
Insert into #SequenceTable values(103)
Insert into #SequenceTable values(105)
Insert into #SequenceTable values(106)
Insert into #SequenceTable values(107)
Insert into #SequenceTable values(114)
Insert into #SequenceTable values(115)
Insert into #SequenceTable values(116)
Insert into #SequenceTable values(129)
Insert into #SequenceTable values(130)
Insert into #SequenceTable values(131)

--Gap identification code
Select LastSeqNumber
, NextSeqNumber
, FirstAvailable = LastSeqNumber + 1
, LastAvailable = NextSeqNumber - 1
, NumbersAvailable = NextSeqNumber - (LastSeqNumber + 1)
from (
Select LastSeqNumber = (Select isnull(Max(Seq2.SeqNumber),0) as SeqNumber
from #SequenceTable Seq2
where Seq2.SeqNumber < Seq1.SeqNumber)
, NextSeqNumber = SeqNumber
from #SequenceTable Seq1
) as A
where NextSeqNumber - LastSeqNumber > 1
order by LastSeqNumber

select * from #SequenceTable

 

[webmigit]

Thanks a lot guys!

ALFII.com

http://www.alfii.com

---------------------
If this post answered or helped to answer your question, please reply with such so that forum members with a similar question will know to use this advice.