CODEWITHSUNDEEP
sqlsql-serversql-server-2014
Luke101
Luke101

Reputation: 65248

How to find gaps in a sequence of numbers in SQL Server

I am trying to find the smallest missing number in this table.

+------+--------+
| id   | number |
+------+--------+
|  902 |    1   |
|  908 |    2   |
| 1007 |    7   |
| 1189 |    8   |
| 1233 |   12   |
| 1757 |   15   |
+------+--------+

In the number column, you can see there are several gaps between the numbers. I need to get the number after the smallest gap. So in the case above I need the number 3. Because 2 is the smallest number after a gap.

Upvotes: 3

Views: 8542

Answers (4)

Sandbergen
Sandbergen

Reputation: 29

This is a nice version which even works in SQL2008! Make sure the ID field is indexed! It returns the number of missing records (gapSize) and the range of the missing records (gapRange)

it can easily be extended with additional information (such as timestamps) by adding t1 fields in the CTE part.

with CTE_NextID as (

    select 
          t1.ID
        , (select top 1 t2.ID from @table t2 where t2.ID > t1.ID order by t2.ID asc) as NextID
    from @table t1

)
select 
      ID
    , NextID
    , cast(ID +1 as varchar(32))+' - '+cast(NextID-1 as varchar(32)) as gapRange
    , nextID - ID - 1 as gapSize
from CTE_NextID 
where NextID > ID + 1
order by ID asc;

Upvotes: 0

Matt
Matt

Reputation: 14341

Lots of different ways here is one that is probably popular these days due to using window functions.

;WITH cte AS (
    SELECT
       Id
       ,[Number]
       ,LAG([Number],1,NULL) OVER (ORDER BY [Number] ASC) AS LagValue
    FROM
       @Table
)

SELECT
    MIN(LagValue) + 1 AS SmallestNumberAfterGap
FROM
    cte
WHERE
    LagValue <> [Number] - 1

And here is one that is less overall code using an LEFT JOIN

SELECT
    MIN(t2.[Number]) + 1 AS SmallestNumberAfterGap
FROM
    @Table t1
    LEFT JOIN @Table t2
    ON t1.Number + 1 = t2.Number

And because I am just writing more code here is one using EXISTS

SELECT
    MIN(t1.Number) + 1 AS SmallestNumberAfterGap
FROM
    @Table t1
WHERE
    NOT EXISTS (SELECT * FROM @Table t2 WHERE t1.Number + 1 = t2.Number)

Here is a link showing all 3 options functioning http://rextester.com/TIFRI87282

Upvotes: 4

TheGameiswar
TheGameiswar

Reputation: 28890

This solution will give you the minimum numbers missing from sequence not a single number .. This uses a numbers table..

Demo Here 

;With cte
as
(
select 
*
from 
Numbers n
left join
#t1
on n.Number=#t1.num
where n.Number<=(select max(num) from #t1)

)
select 
number from cte c1
join
#t1
on #t1.num+1=c1.Number
where c1.num is null

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269693

I would use lead():

select min(id) + 1
from (select t.*,
             lead(id) over (order by id) as next_id
      from t
     ) t
where next_id <> id + 1;

If you want to ensure that the ids start at 1 (so if "1" is missing, then return that), you can do:

select (case when min(minid) <> 1 then 1 else min(id) + 1 end)
from (select t.*, min(id) over () as minid
             lead(id) over (order by id) as next_id
      from t
     ) t
where next_id <> id + 1;

Upvotes: 5

Related Questions