Re: SELECT Question

All,
thanks for the many suggestions
Alex

Manfred Koizar wrote:

>On Thu, 20 Nov 2003 16:52:37 +0900, Alex <alex(at)meerkatsoft(dot)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
>
>
>
>