Re: [GENERAL] SELECT Question

[Alex]

All,
thanks for the many suggestions
Alex
Manfred Koizar wrote:

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 8: explain analyze is your friend