Hi, Nice tricky, but... SET @x:=0; SELECT @x:=t1.id+1 FROM mytable t1 LEFT JOIN mytable t2 ON t1.id+1 = t2.id WHERE t2.id IS NULL LIMIT 1; INSERT INTO mytable SET id=@x,data0='xxxxxxxxx',data1='yyyyyyyy'; Regards, Gelu _____________________________________________________ G.NET SOFTWARE COMPANY
Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] ----- Original Message ----- From: Dan Nelson <[EMAIL PROTECTED]> To: daniel <[EMAIL PROTECTED]> Cc: <[EMAIL PROTECTED]> Sent: Friday, May 03, 2002 1:24 AM Subject: Re: inserting into the lowest possible id > 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 > > --------------------------------------------------------------------- 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