Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2004
    Location
    Canada
    Posts
    57

    Unanswered: Missing index values

    Hi there!

    my_table has 23043 records.
    The next autoindex indicates 23047.
    I have verified the largest index value allocated which is 23046 and which is correct.

    How can I find the two index values which aren't used?
    (so I can use them with new records and therefore have the index values and the number of records having the same value : 23046).

    Many thanks in advance for your help.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please consider changing your strategy

    the purpose of the auto_increment is to provide uniqueness only

    any attempt to compensate for "gaps" in the numbers is wrong and you should try to find another way to do whatever it is you're doing with the numbers
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    agree with Rudi
    autonumber columns / identity columns are system generated.. they have no meaning outside the system itself. if you are trying to force an exteranl meaning onto such a column it will fail.
    if you need to have sequential numbers then you need to develop a stratgey to generate the nnext availabel number.. its been covered inthis trhead, and other trheads on this forum ad nauseam.

    however to answer your question....
    first off are you not bothered about hte third missing number in your set?
    you could send a series of sql queries counting the number fo records in each set untill you find the missing gaps

    the SQL would be something like select count(<mycolumn>) as NoRecs from <mytable> where <myautonumbercolumn> >x and <myautonumbercolumn> < y
    you could do it by writign a bit of quick & dirty code or do it in something like MySQL Query Browser. if you satrted off say with a range of a hundred it woul failry quickly narrow down where the problem is, then make the range say 10's.. equally ytou can size a query window so that the display is in a simple multiple (Id suggest soemthign like displaying 20..40 records.. do a page down untill the last record on the screen is no longer in the sequence you expect. it may be quicker that say writing a bit a of code
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    headlem yuo nede a nwe kyeboadr, yuors kepes swtiching lettesr aruond

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Nah, the keyboard is Ok, it is just a loose nut attached to the keyboard combined with a need for either a spell checker or a proof reader!

    -PatP

  6. #6
    Join Date
    Dec 2005
    Location
    Tilburg, Netherlands
    Posts
    73
    This query will cough up the missing values (and the next in line)

    Code:
    select t.Column + 1
    from   Table t
    where  not exists
    (      select  t2.Column
           from    Table t2
           where   t2.Column = t.Column + 1)
    Good luck.

    Gr,
    Yveau

    > SELECT * FROM users WHERE clue > 0;
    Empty set (0.00 sec)


  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Pat Phelan
    Nah, the keyboard is Ok, it is just a loose nut attached to the keyboard combined with a need for either a spell checker or a proof reader!

    -PatP

    faircopyerhonner.....

    I think that the proble is getting worse.. mebbe Mad Cow Disease is more prevalent than I first thought
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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