Subject Re: Query help: getting non used records in a table
Author Ali Gökçen
Ok, i guess, you don't like my indirect solution.
so, here is your complex command:

lot: lottery table
num: the lottery number in the lottery table
you need an unique index on(num,another_field)

select 1,cast(min(num)-1 as integer) from lot
having min(num)>1
union all
select cast(num+1 as integer),cast((select case when min(num)-1>0
then min(num)-1 else 99999 end from lot where num>ll.num) as integer)
from lot ll
where not exists(select 1 from lot where num=ll.num+1)
and num is not null
union all
select 1,99999 from rdb$database where not exists(select 1 from lot)