--- In firebird-support@yahoogroups.com, "Diego Rodriguez" wrote:

> Hi all,
>
> I have a table LOTTERY with an integer field NUMBER that is also
> primary key. My app will fill this table with random numbers between
> 0 and 99999 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 random 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?

Sure,

select L1.NUMBER+1 FROM LOTTERY L1
where NOT EXISTS(SELECT * FROM LOTTERY L2
WHERE L2.NUMBER = L1.NUMBER+1)
AND L1.NUMBER < 99999

This won't return all unused numbers - if there are two consequtive
free numbers, only the first will be returned.

Though I'd rather write a program that ordered the numbers 1 to 100000
randomly in the first place and then write them into the LOTTERY table
(though adding a PK) at the first draw.

Set