Firebird gets a list with all available IDs
In the table I have records with id 2,4,5,8. How to get a list with values 1,3,6,7. I tried this way
SELECT t1.id + 1
FROM table t1
WHERE NOT EXISTS (
SELECT *
FROM table t2
WHERE t2.id = t1.id + 1
)
but it doesn't work properly. It does not bring all available positions.
Is this possible without another table?
+3
source to share
1 answer
You can get all the missing ID from a recursive CTE like:
with recursive numbers as (
select 1 number
from rdb$database
union all
select number+1
from rdb$database
join numbers on numbers.number < 1024
)
select n.number
from numbers n
where not exists (select 1
from table t
where t.id = n.number)
the condition number < 1024
in my example limits the request to a recursion depth of max 1024. After that, the request will fail. If you need more than 1024 consecutive IDs, you either run the query multiple times, adjusting the spacing of the generated numbers, or thinking in another query that produces sequential numbers without reaching a level of recursion that is not so hard to write.
+4
source to share