find the gaps in a sequence - SQL Server Forums

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)

find the gaps in a sequence

Author

Topic

jimithing1980
Starting Member

3 Posts

Posted - 2004-10-07 : 13:16:26

I've been trying to figure out how to create a query that would list the missing numbers between a high and low number for a field. For example, If I have the recordset below:

1
3
4
6
7
9

I'd like the resulting recordset to be:

2
5
8

Is there a way to achieve this? I need it to be a regular query (no special stored procedures, etc.) and I cannot create another table (tally table). In other words, it must just be standard SQL code run on a single table. If anyone has a solution, it would be greatly appreciated. Thanks.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-10-07 : 13:27:09

quote:
I need it to be a regular query (no special stored procedures, etc.) and I cannot create another table (tally table). In other words, it must just be standard SQL code run on a single table

Can I ask why the limitations for a solution?

Also there is no need to re-post or cross post to get a response.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=40948

Many talented people frequent these forums and will respond to your question.

chadmat
The Chadinator

1974 Posts

Posted - 2004-10-07 : 13:27:09

Use a table variable as the tally table. Your not creating a table.

-Chad

http://www.clrsoft.com

Software built for the Common Language Runtime.

jimithing1980
Starting Member

3 Posts

Posted - 2004-10-07 : 13:59:21

I am using a database (Foxpro) thats sent with a proprietary software package. The query must be run from within this package - and it will not allow the attachment or creation of tables that are not part of the original database. It will allow for select queries from within this group of tables - but nothing else.

ehorn
Master Smack Fu Yak Hacker

1632 Posts

Posted - 2004-10-07 : 14:01:52

What is the largest number expected from this table?

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-07 : 14:10:07

This has limitations, but its a start


Declare @myTable table (n int)
Insert Into @myTable 
Select 1
Union All Select 3
Union All Select 4
Union All Select 6
Union All Select 7
Union All Select 9

Select 
	*
From 
	(
	Select distinct n = A.n+B.n+C.n+D.n
	From 
		(Select n=0 Union All Select * From @myTable) A, 
		(Select n=0 Union All Select * From @myTable) B, 
		(Select n=0 Union All Select * From @myTable) C, 
		(Select n=0 Union All Select * From @myTable) D 
	) Z
Left Join @myTable Y
On Z.n = Y.n
Where Y.n is null
and Z.n between (Select min(n) From @myTable) and (Select max(n) From @myTable)

Corey

VIG
Yak Posting Veteran

86 Posts

Posted - 2004-10-07 : 14:15:49

select t.num +1
from MyTable as t
left join MyTable t1 on t.num+1=tnum
where (t1.num is null)

Seventhnight
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-10-07 : 14:26:31

quote:
Originally posted by VIG

select t.num +1
from MyTable as t
left join MyTable t1 on t.num+1=tnum
where (t1.num is null)

what if it skips 2?

ie
1
4
5
6
...

Corey

Bitz
Starting Member

19 Posts

Posted - 2004-10-07 : 15:07:06

Are you allowed to create a UDF?

If you build a UDF you can just call it from your SELECT statement and return the results you need. (Possibly you can build your tally table in the UDF)

VIG
Yak Posting Veteran

86 Posts

Posted - 2004-10-07 : 15:29:58

quote:
Originally posted by Seventhnight

what if it skips 2?

This is also limitation :)
The query always return first from them

tuenty
Constraint Violating Yak Guru

278 Posts

Posted - 2004-10-07 : 17:05:12

quote:
Originally posted by ehorn

quote:
I need it to be a regular query (no special stored procedures, etc.) and I cannot create another table (tally table). In other words, it must just be standard SQL code run on a single table
Can I ask why the limitations for a solution?

Also there is no need to re-post or cross post to get a response.

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=40948

Many talented people frequent these forums and will respond to your question.

The example in this page works. If you prefer you can modify it so it does not contain temp or variable tables

select ints.i
from 
(select i = i1.i + i2.i + i3.i + i4.i + i5.i + i6.i
 from
  (select i = 0 union select 1) as i1 ,
  (select i = 0 union select 2) as i2 ,
  (select i = 0 union select 4) as i3 ,
  (select i = 0 union select 8) as i4 ,
  (select i = 0 union select 16) as i5 ,
  (select i = 0 union select 32) as i6) as ints 
left outer join 
  (select 1 as i
   union  select 7 as i
   union  select 8 as i
   union  select 9 as i
   union  select 10 as i
   union  select 12 as i
   union  select 20 as i
   union  select 25 as i
   union  select 45 as i
   union  select 60 as i) nos 
on ints.i = nos.i
where nos.i is null and ints.i <= 60 and ints.i <> 0
order by ints.i

Where nos is the table with gaps between numbers and 60 = max(nos.i)

*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*.*

A candle loses nothing by lighting another candle

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources