In the last episode (May 02), daniel said:
> lets say you have a table like this
> 
> id   |   data0   |   data1   |   ...
> 0    |   xxxxx   |   yyyyy   |   ...
> 1    |   xxxxx   |   yyyyy   |   ...
> 2    |   xxxxx   |   yyyyy   |   ...
> 3    |   xxxxx   |   yyyyy   |   ...
> 4    |   xxxxx   |   yyyyy   |   ...
> 7    |   xxxxx   |   yyyyy   |   ...
> 
> what if when i performed an INSERT query again, i wanted that id to
> NOT be 8, but be 5 since it's the lowest available id.  what would i
> do?

SELECT t1.id+1 
FROM mytable t1 LEFT JOIN mytable t2 ON t1.id+1 = t2.id
WHERE t2.id IS NULL;

That will return the first "empty" id in each block of unused ids.  In
your example, it would return

+---------+
| t1.id+1 |
+---------+
|       5 |
|       8 |
+---------+
2 rows in set

If you only want the first row, add a "LIMIT 1" to the end of the query.

-- 
        Dan Nelson
        [EMAIL PROTECTED]

---------------------------------------------------------------------
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/           (the list archive)

To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Reply via email to