It's always a little more challenging to find something that's NOT in the data!
I'd suggest two approaches:
1 - create a reference table containing all the possible values, 1 -
9999999. Then do a SELECT MIN and a join to find the lowest number
not in your data table, something like this:
FROM reference_table r
LEFT JOIN data_table d ON r.uid = d.uid
WHERE d.uid IS NULL
2 - create a stored procedure that starts at 1 and checks for the
presence of each number, adds one, until not present in the table.
This is pretty inefficient though.
On 11/13/06, Kory Wheatley <wheakorypersonal@stripped> wrote:
> I need some advice. We currently are in the process of starting to use LDAP for our
> accounts to authenticate. Now when I create a new LDAP account I need to assign a free
> gidnumber and uidnumber which can be to 1 to 9999999. My plan is to load all gidnumbers
> and uidnumbers that are being used into two separate mysql tables. What command could I
> use to find the first number that is not being used or not in the database?.
> Now sometimes it could be 12 or the very highest number, because when accounts are
> deleted these numbers will be removed from the tables, so I need to find the next
> available number.