Professional Web Applications Themes

Finding lowest free key in table - MySQL

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 ...

  1. #1

    Default Finding lowest free key in table

    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 Guest

  2. #2

    Default Re: Finding lowest free key in table

    com wrote in news:1184003720.436676.169760
    o61g2000hsh.googlegroups.com:
     


    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?


    Good Guest

  3. #3

    Default Re: Finding lowest free key in table

    com wrote: 
    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
    Adam Guest

  4. #4

    Default Re: Finding lowest free key in table

    Am 09.07.2007, 21:50 Uhr, schrieb Good Man <com>:
     
    >
    >
    > 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?
    >
    >[/ref]


    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!
    P.A. Guest

  5. #5

    Default Re: Finding lowest free key in table

    Am 11.07.2007, 01:19 Uhr, schrieb Adam Englander <com>:
     
    > 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[/ref]

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

    Cheers, Pascal.
    P.A. Guest

Similar Threads

  1. Finding the most recent record in a table with otherwiseidentical field values
    By kbergstrom in forum Coldfusion Database Access
    Replies: 7
    Last Post: September 22nd, 08:55 PM
  2. Finding duplicates in an msaccess table
    By Rustywater in forum Coldfusion Database Access
    Replies: 5
    Last Post: April 8th, 11:11 AM
  3. DBI::XBase not finding table files
    By Gary Stainburn in forum PERL Beginners
    Replies: 2
    Last Post: October 14th, 08:22 AM
  4. Finding Duplicate Values in a table
    By Dan in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 17th, 03:30 PM
  5. Finding null values in a large table
    By Tim Dry in forum Oracle Server
    Replies: 5
    Last Post: December 6th, 10:53 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139