List:General Discussion« Previous MessageNext Message »
From:Dan Buettner Date:November 13 2006 7:07pm
Subject:Re: Find next available number for gidnumbers and uidnumbers
View as plain text  
Kory -

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:

SELECT MIN(r.uid)
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.

HTH,
Dan


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.
>
>
>
Thread
Find next available number for gidnumbers and uidnumbersKory Wheatley13 Nov
  • RE: Find next available number for gidnumbers and uidnumbersJerry Schwartz13 Nov
  • Re: Find next available number for gidnumbers and uidnumbersDan Buettner13 Nov