List:General Discussion« Previous MessageNext Message »
From:Gelu Date:May 3 2002 9:00am
Subject:Re: inserting into the lowest possible id
View as plain text  
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 : gg@stripped
                                          gelugogancea@stripped
----- Original Message -----
From: Dan Nelson <dnelson@stripped>
To: daniel <dan@stripped>
Cc: <mysql@stripped>
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
> dnelson@stripped
>
> ---------------------------------------------------------------------
> 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 <mysql-thread107880@stripped>
> To unsubscribe, e-mail <mysql-unsubscribe-ggelu=arctic.ro@stripped>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
>
>

Thread
inserting into the lowest possible iddaniel2 May
  • Re: inserting into the lowest possible idDan Nelson3 May
  • Re: inserting into the lowest possible idGelu3 May