SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    Where's my title at? dreaz's Avatar
    Join Date
    Apr 2004
    Posts
    144
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Help with hard query

    Hi,
    I'm not sure if this is possible, at least I've never seen it, but it's worth the shot.
    I have a table with about 300k rows, and the fileld ID which is auto_increment.

    Say I have:

    ID
    1
    2
    3
    5
    6
    7...

    As you can see, ID 4 is missing, because it was deleted from the table.

    Is there a way to get all the "missing ID's" from the auto_increment field, without having to go through each field checking for it?

    thanks in advance

  2. #2
    reads the ********* Crier silver trophybronze trophy longneck's Avatar
    Join Date
    Feb 2004
    Location
    Tampa, FL (US)
    Posts
    9,854
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Code:
    select t1.id + 1 as missing
    from test t1
    left join test t2 on
     t1.id = t2.id +1
    where t2.id is null
    and t2 < max(select id from test)
    if you don't have mysql 4.1 or newer, then you have to remove the and t2 < max(select id from test) part and just ignore the last result (which will be one higher than the highest id in the table)

  3. #3
    Where's my title at? dreaz's Avatar
    Join Date
    Apr 2004
    Posts
    144
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    It works Thanks.


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
  •