how to check which number is missing..

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

Transact-SQL (2008)

how to check which number is missing..

Author

Topic

asifbhura
Posting Yak Master

165 Posts

Posted - 2011-06-12 : 01:12:08

Hello

I have number in one column from 1 to around 5000...

I want to check which number is missing... in increment

like
as example
1
2
3
4
6

so It should print 5 like this i want to check

regards

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2011-06-12 : 01:34:11

One simple way to do this. There are literally hundreds of ways to accomplish this. The easiest way is to have a tally table.

DECLARE	@Sample TABLE
	(
		Number INT PRIMARY KEY
	)

INSERT	@Sample
	(
		Number
	)
VALUES	(1),
	(2),
	(3),
	(4),
	(6)

;WITH cteSource(Number)
AS (
	SELECT	Number - 1
	FROM	@Sample

	UNION

	SELECT	Number
	FROM	@Sample

	UNION

	SELECT	Number + 1
	FROM	@Sample
)
SELECT		c.Number
FROM		cteSource AS c
LEFT JOIN	@Sample AS s ON s.Number = c.Number
INNER JOIN	(
			SELECT	MIN(Number) AS a,
				MAX(Number) AS b
			FROM	@Sample
		) AS x ON c.Number BETWEEN x.a AND x.b
WHERE		s.Number IS NULL

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

sshelper
Posting Yak Master

216 Posts

Posted - 2011-06-12 : 02:28:07

Here's a link that may be of help:

http://www.sql-server-helper.com/tips/determine-missing-identity-values.aspx

SQL Server Helper
http://www.sql-server-helper.com/sql-server-2008/sql-server-2008-date-format.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources