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@strippedhttp://www.blackstar.co.uk/
Black Star | The UK's Biggest Video & DVD store * Free Postage Worldwide
-----------------------------------------------------------------------------

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.