On Thu, 20 Nov 2003 16:52:37 +0900, Alex <[EMAIL PROTECTED]> 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 ---------------------------(end of broadcast)--------------------------- TIP 3: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly