Re: strange result for select * from table where id=random()*900

On 27.07.2007, at 10:48, Stefan Zweig wrote:

> hi list,
>
> this is my query:
>
> select
> *
> from _g2977
> where
> id=floor(random() * (900));
>
> in table _g2977 i have id(s) from 1 up to 900. i just want to
> select one random dataset by the above query but i does not work.
>
> actually i get sometime zero, sometimes one, sometimes two and
> sometimes even three results back from the above query although i
> thought it should give only one random dataset from the table.
>
> what is wrong?
>
> i appreciate any help.
>
> thanks in advance, stefan
> ______________________________________________________________________
> ___
> In 5 Schritten zur eigenen Homepage. Jetzt Domain sichern und
> gestalten!
> Nur 3,99 EUR/Monat! http://www.maildomain.web.de/?mc=021114
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
SELECT * FROM _g2977 g,
(SELECT floor(random() * (900) as rnd) r
WHERE g.id = r.rnd

should work better, haven't tested though.

Kristo Kaiv
http://kaiv.wordpress.com (PostgreSQL blog)