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
Answer:
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