SQL query to find Missing sequence numbers

Question

Hi,

I have column named "sequence" . I have data in this column like 1,2,3,4,5,7,9,10,15 .

I need to find out missing sequence numbers from table .

what is the sql query to find missing sequence numbers from table ? I am expecting results like 6,8,11,12,13,14

Thanks a lot

EDIT

I just need to return one column like

Missing numbers

6
8
11
12
13
14

I am using only one table

I tried this query but am not getting the expected result.Here is the query

select de.sequence + 1 as sequence from dataentry as de 
left outer join dataentry as de1 on de.sequence + 1 = de1.sequence
where de1.sequence is null  order by sequence asc;

Answer 1

Try with this:

declare @min int
declare @max int

select @min = min(seq_field), @max = max(seq_field) from [Table]

create table #tmp (Field_No int)
while @min <= @max
begin
   if not exists (select * from [Table] where seq_field = @min)
      insert into #tmp (seq_field) values (@min)
   set @min = @min + 1
end
select * from #tmp
drop table #tmp

Answer 2

How about something like:

  select (select isnull(max(val)+1,1) from mydata where val < md.val) as [from],
     md.val - 1 as [to]
  from mydata md
  where md.val != 1 and not exists (
        select 1 from mydata md2 where md2.val = md.val - 1)

giving summarised results:

from        to
----------- -----------
6           6
8           8
11          14

Answer 3

There's a discussion of SQL to solve this sort of problem at http://www.duelec.de/blog/?p=337.

It's not written specifically in sqlserver2005 but it should give you enough info for you to adapt it.

Answer 4

You could also solve using something like a CTE to generate the full sequence:

create table #tmp(sequence int)

insert into #tmp(sequence) values (1)
insert into #tmp(sequence) values (2)
insert into #tmp(sequence) values (3)
insert into #tmp(sequence) values (5)
insert into #tmp(sequence) values (6)
insert into #tmp(sequence) values (8)
insert into #tmp(sequence) values (10)
insert into #tmp(sequence) values (11)
insert into #tmp(sequence) values (14)

DECLARE @max INT SELECT @max = max(sequence) from #tmp; with full_sequence ( Sequence ) as ( SELECT 1 Sequence UNION ALL SELECT Sequence + 1 FROM full_sequence WHERE Sequence

Hmmmm - the formatting is not working on here for some reason? Can anyone see the problem?

Answer 5

Aren't all given solutions way too complex? wouldn't this be much simpler:

SELECT  *
FROM    (SELECT  row_number() over(order by number) as N from master..spt_values) t
where   N not in (select 1 as sequence union  
     select 2 union 
     select 3 union 
     select 4 union 
     select 5 union 
     select 7 union 
     select 10 union 
     select 15
     )

Answer 6

The best solutions above are those that use a temporary table with the sequence. Assuming you build such a table, LEFT JOIN with NULL check should do the job:

SELECT      #sequence.value
FROM        #sequence
LEFT JOIN   MyTable ON #sequence.value = MyTable.value
WHERE       MyTable.value IS NULL

But if you have to repeat this operation often (and more then for 1 sequence in the database), I would create a "static-data" table and have a script to populate it to the MAX(value) of all the tables you need.

EDIT: As pointed by Sam Saffron, this page may give some ideas on how to create (temp) sequence tables.