How to get first gap in a column containing sequential int values

Question

I have a column in a SQL Server database table, there is a column containing integer values and I would like to get the max value unless there is a gap in the series, then I would like to get the first missing value.

For example from the below table, I am looking to get value 4

1
2
null
3

The above is simple, however if the table contains data like below, how can I find which id I am missing, in this case it would be 8

1
3
2
4
null
5
7
6
null
10
9

//////////////////

Edit:

I Initially implemented @podiluska's answer and was going to accept it however when I tested with a table with over 10K rows it was quite slow, so I have taken @vmvadivel's answer and changed it slightly as shown below and it works quite fast, but still not 100% what I wanted:

SELECT Number
FROM 
(
    SELECT ROW_NUMBER() OVER(ORDER BY iValue) AS Number
    FROM tblNumbers WHERE iValue IS NOT NULL
) temp
WHERE Number NOT IN 
(
    SELECT iValue FROM tblNumbers WHERE iValue IS NOT NULL
)

This works quite well however if all the iValue fields are null then I don't get anything back, but I want it to return 1 as it will be the first value in series, obviously I can handle this in the C# code but I am sure there must be a way of returning 1 if the above query does not return a row?

I have changed the top level Select Number with SELECT Coalesce(Number, 1) but it did not work, any ideas how to solve this?

Thanks

You seems to have used tblNumbers in both places. Within the WHERE clause change it to the actual table which has the data you had shown in the question. tblNumbers is supposed to have 1 to n continuous running number in it. — vmvadivel, Jul 18 '12 at 02:47

score 5 · Accepted Answer · answered Jul 17 '12 at 09:31

5

If #t is your table

select min(t1.number)+1 
from #t t1
left join #t t2 
           on t1.number = t2.number-1
where t2.number is null

answered Jul 17 '12 at 09:31

podiluska

50,950
7
98
104

Markus Jarderot · Answer 2 · 2012-07-17T12:21:57.397

SELECT
    t1.number + 1 AS range_start,
    MIN(t2.number) - 1 AS range_end
FROM test t1
INNER JOIN test t2
    ON t2.number > t1.number
WHERE t1.number IS NOT NULL
GROUP BY t1.number
HAVING t1.number + 1 <= MIN(t2.number) - 1

If you add a number 15 to the table, the output will be:

range_start  range_end
          8          8
         11         14

It finds each successive pair of numbers, with a gap of at least one. It then adds or subtracts one from this, so that the start_range and end_range is the range of the gap.

score 1 · Answer 3 · answered Jul 17 '12 at 10:59

1

If you have a number table which has sequential int value in it then you can do something like this:

SELECT * FROM 
(
    SELECT ROW_NUMBER() OVER(ORDER BY iValue) AS Number FROM tblNumbers
) temp
WHERE Number NOT IN 
(
    SELECT [ID] FROM tblFindGaps
)
GO

answered Jul 17 '12 at 10:59

vmvadivel

1,041
5
7

Thanks, even though this is not quite right I think it is a better solution, as it is faster than @podiluska's answer if we have quite a few rows in the table. This does not handle the fact that we can have multiple rows with null value in it. I'll fix this issue and post my own answer below. – 03Usr Jul 17 '12 at 15:56

How to get first gap in a column containing sequential int values

3 Answers3

Linked