Listing Numbers
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
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:
Show quoted text
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
10I want the database to return
4
6
8Any 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