How to find missing numbers in database

Question

Post reply

How to find missing numbers in database

amitsingh308

Ten Centuries

Points: 1287
More actions
December 7, 2009 at 2:12 am

#214553

Hi
I have a table which holds the information of students which hold near about 5000 records. but now i want to check which roll number is missing in my database, which is not enter note all roll numbers are in serial order. as 1-5000 but i found some records are not in database such as information about roll number 4354, so I want to check how many more records are there as '4354' can you tell me??

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

madhuri-425013 Say Hey Kid Points: 679 More actions · Answer 1

Hi,

Can you try the below query.

Select rollno

from tbl

where rollno not between

1 and 5000

Hope it solves your problem.

Thanks,

Madhuri

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 2

First you create a temp table as you said there are 5000 records.

Generate 5000 records

SET NOCOUNT ON

CREATE Table #Temp (RID INT)

CREATE Table #YourTable (ID INT)

DECLARE @I INT

SET @I=1

WHILE @I<=5000

BEGIN

INSERT INTO #Temp SELECT @I

SET @I=@I+1

END

INSERT INTO #YourTable SELECT * FROM #Temp

-- Select Count(*) from #Temp

Assume that this are the values which is missing in your table so i am just deleting it

Delete from #YourTable Where ID In(5,10,15,555,963,93,75,1741,4999,456)

After that you just join the tables and see

--Select Count(*) from #YourTable

Select B.RID,A.ID FROM #YourTable as A RIGHT OUTER JOIN #Temp AS B

ON A.ID=B.RID WHERE A.ID is null

GO

DROP Table #YourTable

DROP Table #Temp

Thanks

Parthi

Thanks
Parthi

amitsingh308 Ten Centuries Points: 1287 More actions · Answer 3

Hi thank You for replying but this query provide the information of these records which are entered in database.

Nabha SSCrazy Eights Points: 8534 More actions · Answer 4

You can use tally table also, does this help?

I think it will return the last record also which is of no importance in this case,

SELECT (U1.ROLLNO + 1)

FROM URTABLE U1

LEFT JOIN URTABLE U2

ON U2.ROLLNO = U1.ROLLNO + 1

Where U2.ROLLNO IS NULL

---------------------------------------------------------------------------------