# sql – How to choose missing IDs?

## Question:

There is an ID field in some Tab table, some numbers are missing, for example: 1,2,4,5,6,8

How to choose numbers 3 and 7?

I made such a request for a long time and now I can’t remember how)) I remember there was ID + 1 in the condition

Algorithm. For each Id, we look at the next one, if it is not equal to the current one + 1, we find a gap. Let there be a table SomeTable, and it has records with id values: 5,7,8,11,15 Let's find skip intervals:

``````SELECT id+1 as start_interval, next_id-1 as finish_interval
FROM(
SELECT id, LEAD(id)OVER(ORDER BY Id)as next_id
FROM SomeTable
)T
WHERE id+1 <> next_id
``````

will give:

``````start_interval  finish_interval
6   6
9   10
12  14
``````

If we are interested in the interval from 1 to the first Id, we will artificially introduce id=0 into the request:

``````SELECT id+1 as start_interval, next_id-1 as finish_interval
FROM(
SELECT id, LEAD(id)OVER(ORDER BY Id)as next_id
FROM (
SELECT 0 Id
UNION ALL
SELECT Id FROM SomeTable
)T
)T
WHERE id+1 <> next_id
``````

will give:

``````start_interval  finish_interval
1   4
6   6
9   10
12  14
``````

Note: I have used MS SQL since version 2012, it should work in Oracle and PostgreSQL too.

For versions of MS SQL below 2012 , you can use the following code, which is almost as efficient as LEAD():

``````SELECT id1+1 start_interval, id2-1 finish_interval
FROM(
SELECT
MAX(CASE WHEN dN=1 THEN Id END)id1,
MAX(CASE WHEN dN=0 THEN Id END)id2
FROM(
SELECT ROW_NUMBER()OVER(ORDER BY Id)as N, Id
FROM SomeTable
)T1 , ( SELECT 0 as dN UNION ALL SELECT 1 )T2
GROUP BY N+dN
)T
WHERE Id1+1 <> Id2
``````

For MySQL it will look something like this

``````SELECT last_id+1 as start_interval, currentId-1 as finish_interval
FROM(
SELECT @Id last_id, @Id := Id currentId
FROM SomeTable, (SELECT @Id := NULL)T
ORDER BY Id
)T
WHERE last_id+1 <> current_id
``````
Scroll to Top