Re: SELECT Question

On Thu, 20 Nov 2003 16:52:37 +0900, Alex <alex@meerkatsoft.com> wrote:
>>>>Is there an easy way to write a select statement that returns me the
>>>>frist free number or any within the range of 200?
>>>>For example if 1-30, and 32-50 are occupied then i would like to fill in
>>>>the new entry with id 31.

Fortunately this is not the performance mailing list :-)

First free number:
SELECT max(t1.id) + 1
  FROM t AS t1 INNER JOIN t AS t2
       ON (t1.id < 200 AND t1.id < t2.id AND t2.id <= 200)
 GROUP BY t2.id
HAVING max(t1.id) + 1 < t2.id
 ORDER BY t2.id
 LIMIT 1;

Make sure that there is always a row with id=0 and a row with id=200.

Any free number:
SELECT id - 1
  FROM t
 WHERE 1 < id AND id <= 200
   AND NOT EXISTS (SELECT * FROM t AS t2 WHERE t2.id = t.id - 1)
 LIMIT 1;

Always having a row with id=200 helps avoid unwanted corner cases.

One more:
SELECT coalesce(max(id), 0) + 1
  FROM t
 WHERE id <= 200
   AND id = (SELECT count(*) FROM t AS t2 WHERE t2.id <= t.id);

This should work without any dummy rows.  And it will not work, if id
is not unique or there is any row with id < 1.

Servus
 Manfred