Subject Re: Query help: getting non used records in a table
Author Ali Gökçen
Hi Diego,

I wrote a single sql command here to find out empty numbers but it
was not optimistic i think. ( I dont like to do heavy load FB ;) )

Try this one:

Create table lotarynumbers
thenumber integer not null primary key check(value between 1 and


99999 times, insert into lotarynumbers(thenumber) values(gen_id

select thenumber as emptynumber from lotarynumbers ln
where not exists(select 1 from mylottarytable where
order by thenumber


--- In, "Diego Rodriguez"
<drodriguez@a...> wrote:
> Hi all,
> I have a table LOTTERY with an integer field NUMBER that is also
> key. My app will fill this table with random numbers between 0 and
> everytime that is needed. When an event happens I generate a
random number
> and try to insert that number. Because it is for a lottery, there
must not
> be repeated numbers, and this is ensured by primary key. If the
> number is repeated, an exception "violation of primary key" is
raised, and I
> try with another random number. I know this model will have
problems when
> the table is almost full, because I will get a lot of exceptions
each time
> an insert is made, so if the exception is raised twice, I search
for a non
> used number in the table (lottery not really random, but will
work). The way
> I get this numbers is getting all numbers ordered and
programatically find
> any gap between numbers...
> It is posibble to get a non used number directly with a query?
> I'm using Firebird 1.0.3 in a Debian Linux platform.
> Many thanks
> Diego