List:General Discussion« Previous MessageNext Message »
From:tony Date:September 18 1999 12:41pm
Subject:How To INSERT at first free space, rather than at end
View as plain text  
There are certain circumstances where we would want to insert into a table
at the first "free" point, rather than at the end: e.g. with a table such:

+-----+--------+
| box |     id |
+-----+--------+
|   1 |      8 |
|   2 |      9 |
|   3 |     50 |
|   4 |     87 |
|   5 |     18 |
|   7 |  14511 |
|   8 |   1218 |
|   9 |      9 |
|  10 |     50 |
+-----+--------+

we'd want to insert at box 6, rather than 11.

At first glance this seemed like quite a straightforward thing to do,
but we've had a lot of difficulty coming up with an elegant way to do it!

Solution 1 was to do something along the lines of:

 SELECT b1.box as boxid1, MIN(b2.box) as boxid2
   FROM box b1, box b2
  WHERE b1.box < b2.box
  GROUP BY b1.box
 HAVING boxid2 - boxid1 > 1
  LIMIT 1

And then inserting at boxid1 + 1.

But this gets ridiculously slow as the number of boxes increase.

So the second solution was to create a table "number" with one column
which just consisted of the numbers 1 - 10,000, then doing a:

 SELECT number 
   FROM numbers 
   LEFT JOIN box ON box = number 
  WHERE ISNULL(box) 
  ORDER BY number
  LIMIT 1

which is VERY fast, but needs this ridiculous extra table.

Surely there must be a way that doesn't need an extra table, which is
this fast?

Anyone?

Tony
-- 
-----------------------------------------------------------------------------
 Tony Bowden | tony@stripped               http://www.blackstar.co.uk/
  Black Star |    The UK's Biggest Video & DVD store * Free Postage Worldwide
-----------------------------------------------------------------------------


Thread
How To INSERT at first free space, rather than at endtony18 Sep
  • Re: How To INSERT at first free space, rather than at endMartin Ramsch18 Sep
    • Re: How To INSERT at first free space, rather than at endDan Nelson18 Sep
  • Re: How To INSERT at first free space, rather than at endBob Kline18 Sep