Brian,
If it is for the purpose of finding a free entry, it would be more
practical to have all possible records (1-9999) exist in the table and
simply flag them as valid or used or free (as you prefer). Then finding
the first available is as simple as:
SELECT MIN(n) FROM list WHERE free;
or
SELECT n FORM list WHERE free LIMIT 1;
If you insist in geting the list of unused:
Create a single column table t19999 with with row 1 to 9999.
Then:
SELECT n FROM t19999 WHERE NOT EXISTS( SELECT n FROM list WHERE list.n =
t19999.n);
JLL
Brian Avis wrote:
>
> If I have a column with a list of numbers between 1 and 9999 is there a
> way to select all the numbers in that range that have not been used?
>
> For example lets say the list is 1 - 10 instead, and looked like this.
>
> 1
> 2
> 3
> 5
> 7
> 9
> 10
>
> I want the database to return
>
> 4
> 6
> 8
>
> Any hints or ideas?
>
> --
> Brian Avis
> SEARHC Medical Clinic
> Juneau, AK 99801
> (907) 463-4049
> cd /pub
> more beer
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster