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
