How to retrive records by number

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 2005 Forums

Transact-SQL (2005)

How to retrive records by number

Author

Topic

Mng
Yak Posting Veteran

59 Posts

Posted - 2009-08-27 : 03:20:12

Hi, i have a student table with 100 records. The table contains column "SNO" which is an int field starts with 101 and ends with 200. i have deleted randomly some of the student records.

Now i want to know what are the Students i had deleted( I mean i wnat SNO)

SNO SNAME
101 abc
102 deef
105 ght
115 456
------so on
200 xyz

Now i want the missing SNO from the table. how to query for this.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-27 : 03:45:43

Search for Find gaps+SQL Server in google

Madhivanan

Failing to plan is Planning to fail

waterduck
Aged Yak Warrior

982 Posts

Posted - 2009-08-27 : 04:35:55

[code]SELECT n.number, s.SNO
FROM master..spt_values n LEFT JOIN Students s ON n.number = s.SNO
WHERE n.type = 'P' AND
n.number >= 101 AND
n.number <= 200 AND
s.SNO IS NULL [/code]
quick but not lasting solution

Hope can help...but advise to wait pros with confirmation...

Mng
Yak Posting Veteran

59 Posts

Posted - 2009-08-27 : 06:02:13

Thank you

quote:
Originally posted by waterduck

SELECT n.number, s.SNO
FROM master..spt_values n LEFT JOIN Students s ON n.number = s.SNO
WHERE n.type = 'P' AND
n.number >= 101 AND
n.number <= 200 AND
s.SNO IS NULL

quick but not lasting solution

Hope can help...but advise to wait pros with confirmation...

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-08-27 : 06:27:08

create table serialno(sno int)

declare @i int
set @i=101
while @i<=200
begin
insert into serialno values(@i)
set @i=@i+1
end

select * from serialno

delete from serialno where sno in(101,105,145,123,178,136,111,198,123,145)

select number as Sno from master..spt_values where number not in(select sno from serialno) and type='P' and number between 101 and 200

iF theRe iS a wAy iN tHen theRe iS a wAy oUt..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources