List:General Discussion« Previous MessageNext Message »
From:Martin Ramsch Date:September 18 1999 2:22pm
Subject:Re: How To INSERT at first free space, rather than at end
View as plain text  
On Sat, 1999-09-18 14:41:13 +0200, tony@stripped wrote:
> 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.

With indexes on 'box', the following query is the best I can think of
without using a second table:

  SELECT b1.box+1 AS box
  FROM   box AS b1 LEFT JOIN box AS b2
         ON b2.box = b1.box+1
  WHERE b2.box IS NULL
  ORDER BY b1.box
  LIMIT 1;

But this still is way to slow ...

I think, without changing your table design the best you can do is
  SELECT box FROM box;
and just search for the first free number yourself ...

Regards,
  Martin
-- 
Martin Ramsch <m.ramsch@stripped> <URL: http://home.pages.de/~ramsch/ >
PGP KeyID=0xE8EF4F75 FiPr=52 44 5E F3 B0 B1 38 26  E4 EC 80 58 7B 31 3A D7
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