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