Finding Gaps in Sequential Numbers

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

SQL Server Development (2000)

Finding Gaps in Sequential Numbers

Author

Topic

Slickrock
Starting Member

1 Post

Posted - 2005-02-28 : 17:20:16

I have 600,000 records in my table and I need to find where there are gaps and how big the gaps are in a numerical field. Is there a feature or a query I can create to accomplish this? Any help is appreciated!

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2005-02-28 : 17:37:23

Load a table with a sequential series of numbers for the range you you want to compare, and then run a query like this to find the missing values in your table.


select
	a.number
from
	number_table a
	left join
	mytable b
	on a.number = b.number
where
	b.number is null
order by
	a.number

quote:
Originally posted by Slickrock

I have 600,000 records in my table and I need to find where there are gaps and how big the gaps are in a numerical field. Is there a feature or a query I can create to accomplish this? Any help is appreciated!

Codo Ergo Sum

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources