SQL query to find Missing sequence numbers

Question

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

I need to find the missing sequence numbers from the table. What SQL query will find the missing sequence numbers from my table? I am expecting results like

Missing numbers
---------------
6  
8  
11  
12  
13  
14  

I am using only one table. I tried the query below, but am not getting the results I want.

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

This query generates a sequence that ranges from 1 to 15. (Change according to your desired range)

WITH AllNumbers AS (
  SELECT 1 AS number
  UNION ALL
  SELECT number + 1
  FROM dataentry
  WHERE number < 15
)

SELECT number
FROM AllNumbers
WHERE number NOT IN (SELECT number FROM dataentry)

By selecting the numbers not in the cte expression you would get the missing numbers.

Answer 2

The best solutions 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
            AND #sequence.value < (SELECT MAX(MyTable.value) as max_value FROM MyTable)

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.

Answer 3

i had made a proc so you can send the table name and the key and the result is a list of missing numbers from the given table

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create PROCEDURE [dbo].[action_FindMissing_Autoincremnt]
(
@tblname as nvarchar(50),
@tblKey as nvarchar(50)
)
AS
BEGIN
    SET NOCOUNT ON;

declare @qry nvarchar(4000)



set @qry = 'declare @min int ' 
set @qry = @qry + 'declare @max int '

set @qry = @qry +'select @min = min(' + @tblKey + ')'
set @qry = @qry + ', @max = max('+ @tblKey +') '
set @qry = @qry + ' from '+ @tblname 

set @qry = @qry + ' create table #tmp (Field_No int)
while @min <= @max
begin
   if not exists (select * from '+ @tblname +' where '+ @tblKey +' = @min)
      insert into #tmp (Field_No) values (@min)
   set @min = @min + 1
end
select * from #tmp order by Field_No
drop table #tmp '

exec sp_executesql @qry 

END
GO

Answer 4

SELECT TOP 1 (Id + 1)
FROM CustomerNumberGenerator
WHERE (Id + 1) NOT IN ( SELECT Id FROM CustomerNumberGenerator )

Working on a customer number generator for my company. Not the most efficient but definitely most readable

The table has one Id column. The table allows for Ids to be inserted at manually by a user off sequence. The solution solves the case where the user decided to pick a high number

Answer 5

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 (Field_No) values (@min)
   set @min = @min + 1
end
select * from #tmp
drop table #tmp

Answer 6

 -- This will return better Results
    -- ----------------------------------
    ;With CTERange
    As (
    select (select isnull(max(ArchiveID)+1,1) from tblArchives where ArchiveID < md.ArchiveID) as [from],
         md.ArchiveID - 1 as [to]
      from tblArchives md
      where md.ArchiveID != 1 and not exists (
            select 1 from tblArchives md2 where md2.ArchiveID = md.ArchiveID - 1)
    ) SELECT [from], [to], ([to]-[from])+1 [total missing]
    From CTERange 
    ORDER BY ([to]-[from])+1 DESC;


from     to     total missing
------- ------- --------------
6        6      1 
8        8      1
11       14     4

Answer 7

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 < @max
    )

    SELECT
        full_sequence.sequence
    FROM
        full_sequence
    LEFT JOIN
        #tmp
    ON
        full_sequence.sequence = #tmp.sequence
    WHERE
        #tmp.sequence IS NULL

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

Answer 8

Create a useful Tally table:

-- can go up to 4 million or 2^22
select top 100000 identity(int, 1, 1) Id
into Tally
from master..spt_values
cross join master..spt_values

Index it, or make that single column as PK. Then use EXCEPT to get your missing number.

select Id from Tally where Id <= (select max(Id) from TestTable)
except
select Id from TestTable

Answer 9

DECLARE @MaxID INT = (SELECT MAX(timerecordid) FROM dbo.TimeRecord)

SELECT SeqID AS MissingSeqID
FROM (SELECT ROW_NUMBER() OVER (ORDER BY column_id) SeqID from sys.columns) LkUp
LEFT JOIN dbo.TimeRecord t ON t.timeRecordId = LkUp.SeqID
WHERE t.timeRecordId is null and SeqID < @MaxID

I found this answer here: http://sql-developers.blogspot.com/2012/10/how-to-find-missing-identitysequence.html

I was looking for a solution and found many answers. This is the one I used and it worked very well. I hope this helps anyone looking for a similar answer.

Answer 10

Just for fun, I decided to post my solution.
I had an identity column in my table and I wanted to find missing invoice numbers. I reviewed all the examples I could find but they were not elegant enough.

CREATE VIEW EENSkippedInvoicveNo
AS

SELECT CASE WHEN MSCNT = 1 THEN CAST(MSFIRST AS VARCHAR (8)) ELSE
    CAST(MSFIRST AS VARCHAR (8)) + ' - ' + CAST(MSlAST AS VARCHAR (8))  END AS MISSING,
MSCNT, INV_DT  FROM ( 
select  invNo+1  as Msfirst, inv_no -1 as Mslast, inv_no - invno -1 as msCnt, dbo.fmtdt(Inv_dt)  AS INV_dT
from (select inv_no as invNo,  a4glidentity + 1  as a4glid 
from oehdrhst_sql where inv_dt > 20140401) as s
inner Join oehdrhst_sql as h
on a4glid = a4glidentity 
where inv_no - invno <> 1
) AS SS

Answer 11

This is my interpretation of this issue, placing the contents in a Table variable that I can easily access in the remainder of my script.

DECLARE @IDS TABLE (row int, ID int)

INSERT INTO @IDS
select      ROW_NUMBER() OVER (ORDER BY x.[Referred_ID]), x.[Referred_ID] FROM
(SELECT      b.[Referred_ID] + 1 [Referred_ID]
FROM        [catalog].[dbo].[Referrals] b) as x
LEFT JOIN   [catalog].[dbo].[Referrals] a ON x.[Referred_ID] = a.[Referred_ID]
WHERE       a.[Referred_ID] IS NULL

select * from @IDS

Answer 12

Here is a script to create a stored procedure that returns missing sequential numbers for a given date range.

CREATE PROCEDURE dbo.ddc_RolledBackOrders 
-- Add the parameters for the stored procedure here
@StartDate DATETIME ,
@EndDate DATETIME
AS 
    BEGIN

    SET NOCOUNT ON;

    DECLARE @Min BIGINT
    DECLARE @Max BIGINT
    DECLARE @i BIGINT

    IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL 
        BEGIN
            DROP TABLE #TempTable
        END

    CREATE TABLE #TempTable
        (
          TempOrderNumber BIGINT
        )

    SELECT  @Min = ( SELECT MIN(ordernumber)
                     FROM   dbo.Orders WITH ( NOLOCK )
                     WHERE OrderDate BETWEEN @StartDate AND @EndDate)
    SELECT  @Max = ( SELECT MAX(ordernumber)
                     FROM   dbo.Orders WITH ( NOLOCK )
                     WHERE OrderDate BETWEEN @StartDate AND @EndDate)
    SELECT  @i = @Min

    WHILE @i <= @Max 
        BEGIN
            INSERT  INTO #TempTable
                    SELECT  @i

            SELECT  @i = @i + 1

        END

    SELECT  TempOrderNumber
    FROM    #TempTable
            LEFT JOIN dbo.orders o WITH ( NOLOCK ) ON tempordernumber = o.OrderNumber
    WHERE   o.OrderNumber IS NULL

END

GO

Answer 13

SELECT CASE WHEN MAX(column_name) = COUNT(*)
THEN CAST(NULL AS INTEGER)
-- THEN MAX(column_name) + 1 as other option
WHEN MIN(column_name) > 1
THEN 1
WHEN MAX(column_name) <> COUNT(*)
THEN (SELECT MIN(column_name)+1
FROM table_name
WHERE (column_name+ 1)
NOT IN (SELECT column_name FROM table_name))
ELSE NULL END
FROM table_name;

Answer 14

DECLARE @TempSujith TABLE
(MissingId int)

Declare @Id Int
DECLARE @mycur CURSOR
SET @mycur = CURSOR FOR Select  Id From tbl_Table

OPEN @mycur

FETCH NEXT FROM @mycur INTO @Id
Declare @index int
Set @index = 1
WHILE @@FETCH_STATUS = 0
BEGIN
    if (@index < @Id)
    begin
        while @index < @Id
        begin
            insert into @TempSujith values (@index)
            set @index = @index + 1
        end
    end
    set @index = @index + 1
FETCH NEXT FROM @mycur INTO @Id
END
Select Id from tbl_Table
select MissingId from @TempSujith

Answer 15

I know this is a very old post but I wanted to add this solution that I found HERE so that I can find it easier:

WITH Missing (missnum, maxid)
AS
(
 SELECT 1 AS missnum, (select max(id) from @TT)
 UNION ALL
 SELECT missnum + 1, maxid FROM Missing
 WHERE missnum < maxid
)
SELECT missnum
FROM Missing
LEFT OUTER JOIN @TT tt on tt.id = Missing.missnum
WHERE tt.id is NULL
OPTION (MAXRECURSION 0); 

Answer 16

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 17

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