How do I find a "gap" in running counter with SQL?

The following solution:

• provides test data;
• an inner query that produces other gaps; and
• it works in SQL Server 2012.

Numbers the ordered rows sequentially in the "with" clause and then reuses the result twice with an inner join on the row number, but offset by 1 so as to compare the row before with the row after, looking for IDs with a gap greater than 1. More than asked for but more widely applicable.

create table #ID ( id integer );

insert into #ID values (1),(2),    (4),(5),(6),(7),(8),    (12),(13),(14),(15);

with Source as (
    select
         row_number()over ( order by A.id ) as seq
        ,A.id                               as id
    from #ID as A WITH(NOLOCK)
)
Select top 1 gap_start from (
    Select 
         (J.id+1) as gap_start
        ,(K.id-1) as gap_end
    from       Source as J
    inner join Source as K
    on (J.seq+1) = K.seq
    where (J.id - (K.id-1)) <> 0
) as G

The inner query produces:

gap_start   gap_end

3           3

9           11

The outer query produces:

gap_start

3

I wrote up a quick way of doing it. Not sure this is the most efficient, but gets the job done. Note that it does not tell you the gap, but tells you the id before and after the gap (keep in mind the gap could be multiple values, so for example 1,2,4,7,11 etc)

I'm using sqlite as an example

If this is your table structure

create table sequential(id int not null, name varchar(10) null);

and these are your rows

id|name
1|one
2|two
4|four
5|five
9|nine

The query is

select a.* from sequential a left join sequential b on a.id = b.id + 1 where b.id is null and a.id <> (select min(id) from sequential)
union
select a.* from sequential a left join sequential b on a.id = b.id - 1 where b.id is null and a.id <> (select max(id) from sequential);

https://gist.github.com/wkimeria/7787ffe84d1c54216f1b320996b17b7e

Here is an alternative to show the range of all possible gap values in portable and more compact way :

Assume your table schema looks like this :

> SELECT id FROM your_table;
+-----+
| id  |
+-----+
|  90 |
| 103 |
| 104 |
| 118 |
| 119 |
| 120 |
| 121 |
| 161 |
| 162 |
| 163 |
| 185 |
+-----+

To fetch the ranges of all possible gap values, you have the following query :

• The subquery lists pairs of ids, each of which has the lowerbound column being smaller than upperbound column, then use GROUP BY and MIN(m2.id) to reduce number of useless records.
• The outer query further removes the records where lowerbound is exactly upperbound - 1
• My query doesn't (explicitly) output the 2 records (YOUR_MIN_ID_VALUE, 89) and (186, YOUR_MAX_ID_VALUE) at both ends, that implicitly means any number in both of the ranges hasn't been used in your_table so far.

> SELECT  m3.lowerbound + 1, m3.upperbound - 1 FROM
  (
    SELECT m1.id as lowerbound, MIN(m2.id) as upperbound FROM
    your_table m1 INNER JOIN your_table
    AS m2 ON m1.id < m2.id GROUP BY m1.id
  )
  m3 WHERE m3.lowerbound < m3.upperbound - 1;

+-------------------+-------------------+
| m3.lowerbound + 1 | m3.upperbound - 1 |
+-------------------+-------------------+
|                91 |               102 |
|               105 |               117 |
|               122 |               160 |
|               164 |               184 |
+-------------------+-------------------+

If You use Firebird 3 this is most elegant and simple:

select RowID
  from (
    select `ID_Column`, Row_Number() over(order by `ID_Column`) as RowID
      from `Your_Table`
        order by `ID_Column`)
    where `ID_Column` <> RowID
    rows 1

            -- PUT THE TABLE NAME AND COLUMN NAME BELOW
            -- IN MY EXAMPLE, THE TABLE NAME IS = SHOW_GAPS AND COLUMN NAME IS = ID

            -- PUT THESE TWO VALUES AND EXECUTE THE QUERY

            DECLARE @TABLE_NAME VARCHAR(100) = 'SHOW_GAPS'
            DECLARE @COLUMN_NAME VARCHAR(100) = 'ID'


            DECLARE @SQL VARCHAR(MAX)
            SET @SQL = 
            'SELECT  TOP 1
                    '+@COLUMN_NAME+' + 1
            FROM    '+@TABLE_NAME+' mo
            WHERE   NOT EXISTS
                    (
                    SELECT  NULL
                    FROM    '+@TABLE_NAME+' mi 
                    WHERE   mi.'+@COLUMN_NAME+' = mo.'+@COLUMN_NAME+' + 1
                    )
            ORDER BY
                    '+@COLUMN_NAME

            -- SELECT @SQL

            DECLARE @MISSING_ID TABLE (ID INT)

            INSERT INTO @MISSING_ID
            EXEC (@SQL)

            --select * from @MISSING_ID

            declare @var_for_cursor int
            DECLARE @LOW INT
            DECLARE @HIGH INT
            DECLARE @FINAL_RANGE TABLE (LOWER_MISSING_RANGE INT, HIGHER_MISSING_RANGE INT)
            DECLARE IdentityGapCursor CURSOR FOR   
            select * from @MISSING_ID
            ORDER BY 1;  

            open IdentityGapCursor

            fetch next from IdentityGapCursor
            into @var_for_cursor

            WHILE @@FETCH_STATUS = 0  
            BEGIN
            SET @SQL = '
            DECLARE @LOW INT
            SELECT @LOW = MAX('+@COLUMN_NAME+') + 1 FROM '+@TABLE_NAME
                    +' WHERE '+@COLUMN_NAME+' < ' + cast( @var_for_cursor as VARCHAR(MAX))

            SET @SQL = @sql + '
            DECLARE @HIGH INT
            SELECT @HIGH = MIN('+@COLUMN_NAME+') - 1 FROM '+@TABLE_NAME
                    +' WHERE '+@COLUMN_NAME+' > ' + cast( @var_for_cursor as VARCHAR(MAX))

            SET @SQL = @sql + 'SELECT @LOW,@HIGH'

            INSERT INTO @FINAL_RANGE
             EXEC( @SQL)
            fetch next from IdentityGapCursor
            into @var_for_cursor
            END

            CLOSE IdentityGapCursor;  
            DEALLOCATE IdentityGapCursor;  

            SELECT ROW_NUMBER() OVER(ORDER BY LOWER_MISSING_RANGE) AS 'Gap Number',* FROM @FINAL_RANGE

Found most of approaches run very, very slow in mysql. Here is my solution for mysql < 8.0. Tested on 1M records with a gap near the end ~ 1sec to finish. Not sure if it fits other SQL flavours.

SELECT cardNumber - 1
FROM
    (SELECT @row_number := 0) as t,
    (
        SELECT (@row_number:=@row_number+1), cardNumber, cardNumber-@row_number AS diff
        FROM cards
        ORDER BY cardNumber
    ) as x
WHERE diff >= 1
LIMIT 0,1

If your counter is starting from 1 and you want to generate first number of sequence (1) when empty, here is the corrected piece of code from first answer valid for Oracle:

SELECT
  NVL(MIN(id + 1),1) AS gap
FROM
  mytable mo  
WHERE 1=1
  AND NOT EXISTS
      (
       SELECT  NULL
       FROM    mytable mi 
       WHERE   mi.id = mo.id + 1
      )
  AND EXISTS
     (
       SELECT  NULL
       FROM    mytable mi 
       WHERE   mi.id = 1
     )  

DECLARE @Table AS TABLE(
[Value] int
)

INSERT INTO @Table ([Value])
VALUES
 (1),(2),(4),(5),(6),(10),(20),(21),(22),(50),(51),(52),(53),(54),(55)
 --Gaps
 --Start    End     Size
 --3        3       1
 --7        9       3
 --11       19      9
 --23       49      27


SELECT [startTable].[Value]+1 [Start]
     ,[EndTable].[Value]-1 [End]
     ,([EndTable].[Value]-1) - ([startTable].[Value]) Size 
 FROM 
    (
SELECT [Value]
    ,ROW_NUMBER() OVER(PARTITION BY 1 ORDER BY [Value]) Record
FROM @Table
)AS startTable
JOIN 
(
SELECT [Value]
,ROW_NUMBER() OVER(PARTITION BY 1 ORDER BY [Value]) Record
FROM @Table
)AS EndTable
ON [EndTable].Record = [startTable].Record+1
WHERE [startTable].[Value]+1 <>[EndTable].[Value]

If the numbers in the column are positive integers (starting from 1) then here is how to solve it easily. (assuming ID is your column name)

    SELECT TEMP.ID 
    FROM (SELECT ROW_NUMBER() OVER () AS NUM FROM 'TABLE-NAME') AS TEMP 
    WHERE ID NOT IN (SELECT ID FROM 'TABLE-NAME')
    ORDER BY 1 ASC LIMIT 1