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