Hello,

I have a table with staff member data. For obligatory historic
reasons, each staff member has a unique number assigned, which is
taken as primary key. For the same historic reasons the list of staff
numbers is not complete, i.e. there are gaps inbetween. When a new
staff member is introduced, I want to assign the lowest free number to
him or her. How can I find the lowest free integer primary key using
MySQL?

Example:

(staff_number, ...some more staff member information left off)
1
2
5
60
61
63

The lowest free staff_number is 3. Is it possible to do this entirely
in mysql?

I searched around a lot, but as I am fairly new to mysql I probably
did not search for the right terms, so I could not find enlightenment
yet.

Thanks a lot for any hint/advice/sample script!

Cheers, Pascal.


--
Das Bestreben, den Menschen gefällig zu sein,
lässt jede geistige Blüte verwelken.

("Baudolino", Umberto Eco)

anschouwe@yahoo.com wrote in news:1184003720.436676.169760
@o61g2000hsh.googlegroups.com:

> Hello,
>
> I have a table with staff member data. For obligatory historic
> reasons, each staff member has a unique number assigned, which is
> taken as primary key. For the same historic reasons the list of staff
> numbers is not complete, i.e. there are gaps inbetween. When a new
> staff member is introduced, I want to assign the lowest free number to
> him or her. How can I find the lowest free integer primary key using
> MySQL?
>
> Example:
>
> (staff_number, ...some more staff member information left off)
> 1
> 2
> 5
> 60
> 61
> 63
>
> The lowest free staff_number is 3. Is it possible to do this entirely
> in mysql?
>
> I searched around a lot, but as I am fairly new to mysql I probably
> did not search for the right terms, so I could not find enlightenment
> yet.
>
> Thanks a lot for any hint/advice/sample script!


Why do you want to insert them at "3" and not "64"?

It seems your database was set-up to "auto-increment" the first column.
Part of the point of such a set-up is that once staff member 3 is
removed, nobody else can take that key because other tables might be
associated with it. Even if not, what benefit is there to enter with a
key of 3 versus 64?

anschouwe@yahoo.com wrote:
> Hello,
>
> I have a table with staff member data. For obligatory historic
> reasons, each staff member has a unique number assigned, which is
> taken as primary key. For the same historic reasons the list of staff
> numbers is not complete, i.e. there are gaps inbetween. When a new
> staff member is introduced, I want to assign the lowest free number to
> him or her. How can I find the lowest free integer primary key using
> MySQL?
>
> Example:
>
> (staff_number, ...some more staff member information left off)
> 1
> 2
> 5
> 60
> 61
> 63
>
> The lowest free staff_number is 3. Is it possible to do this entirely
> in mysql?
>
> I searched around a lot, but as I am fairly new to mysql I probably
> did not search for the right terms, so I could not find enlightenment
> yet.
>
> Thanks a lot for any hint/advice/sample script!
>
> Cheers, Pascal.
>
>
> --
> Das Bestreben, den Menschen gefällig zu sein,
> lässt jede geistige Blüte verwelken.
>
> ("Baudolino", Umberto Eco)
>
There may be a more efficient way that someone else can come up with but
this will work:

select min(a.incid) as newid from (select id+1 as incid from test) a
where a.incid not in (select id from test);

"test" is the table holding the staff number and id is the staff number.

Adam

Am 09.07.2007, 21:50 Uhr, schrieb Good Man <heyho@letsgo.com>:

> anschouwe@yahoo.com wrote in news:1184003720.436676.169760
> @o61g2000hsh.googlegroups.com:
>
>> Hello,
>>
>> I have a table with staff member data. For obligatory historic
>> reasons, each staff member has a unique number assigned, which is
>> taken as primary key. For the same historic reasons the list of staff
>> numbers is not complete, i.e. there are gaps inbetween. When a new
>> staff member is introduced, I want to assign the lowest free number to
>> him or her. How can I find the lowest free integer primary key using
>> MySQL?
>>
>> Example:
>>
>> (staff_number, ...some more staff member information left off)
>> 1
>> 2
>> 5
>> 60
>> 61
>> 63
>>
>> The lowest free staff_number is 3. Is it possible to do this entirely
>> in mysql?
>>
>> I searched around a lot, but as I am fairly new to mysql I probably
>> did not search for the right terms, so I could not find enlightenment
>> yet.
>>
>> Thanks a lot for any hint/advice/sample script!
>
>
> Why do you want to insert them at "3" and not "64"?
>
> It seems your database was set-up to "auto-increment" the first column.
> Part of the point of such a set-up is that once staff member 3 is
> removed, nobody else can take that key because other tables might be
> associated with it. Even if not, what benefit is there to enter with a
> key of 3 versus 64?
>
>


Second try - my first answer didn't get through... :-/

The primary key is not set to "auto-increment", exactly for the reason you
name. Numbers that get freed shall be used later again, so the entire
entry in the table is removed when a staff member leaves. As staff members
come and go asynchronously, numbers may stay unused a while, or some more
numbers are used. The numbers are used in another context as well (think
of locker numbers, internal snail mail post boxes, etc.) and are
"inherited" from the predecessor. Therefore the amount of numbers is to be
kept as small as possible, otherwise it would increase indefinitely over
time. So the "free" numbers are like a "pool" of numbers, and the
currently smallest one shall be selected for every new staff member.
I should have made this clearer beforehand.

Thanks anyway!

Am 11.07.2007, 01:19 Uhr, schrieb Adam Englander <adamenglander@yahoo.com>:

> anschouwe@yahoo.com wrote:
>> Hello,
>> I have a table with staff member data. For obligatory historic
>> reasons, each staff member has a unique number assigned, which is
>> taken as primary key. For the same historic reasons the list of staff
>> numbers is not complete, i.e. there are gaps inbetween. When a new
>> staff member is introduced, I want to assign the lowest free number to
>> him or her. How can I find the lowest free integer primary key using
>> MySQL?
>> Example:
>> (staff_number, ...some more staff member information left off)
>> 1
>> 2
>> 5
>> 60
>> 61
>> 63
>> The lowest free staff_number is 3. Is it possible to do this entirely
>> in mysql?
>> I searched around a lot, but as I am fairly new to mysql I probably
>> did not search for the right terms, so I could not find enlightenment
>> yet.
>> Thanks a lot for any hint/advice/sample script!
>> Cheers, Pascal.
>> --
>> Das Bestreben, den Menschen gefällig zu sein,
>> lässt jede geistige Blüte verwelken.
>> ("Baudolino", Umberto Eco)
>>
> There may be a more efficient way that someone else can come up with but
> this will work:
>
> select min(a.incid) as newid from (select id+1 as incid from test) a
> where a.incid not in (select id from test);
>
> "test" is the table holding the staff number and id is the staff number.
>
> Adam

It is great, it works! Thanks a lot! :-)))

Cheers, Pascal.