Thank you...this is what I'm considering now, having an auxiliar table with
all numbers placed randomly with a surrogate key and going through the table
following primary surrogate key, because the other solution is not working
as expected (specification problems)

Many thanks Svein and Ali for your help

Diego

----- Original Message -----
From: "Svein Erling Tysvær" <svein.erling.tysvaer@...>
To: <firebird-support@yahoogroups.com>
Sent: Friday, October 21, 2005 10:54 AM
Subject: [firebird-support] Re: Query help: getting non used records in a
table


> --- 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
>
>
>
>
>
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://firebird.sourceforge.net and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Yahoo! Groups Links
>
>
>
>
>
>
>
>
>