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

On 07/27/2007 09:48 AM, 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.

random() isn't immutable, so it's re-calculated for every row. Try f.ex
this instead:
SELECT
*
FROm _g2977
ORDER BY random()
LIMIT 1

--
Tommy Gildseth
DBA, Gruppe for databasedrift
Universitetet i Oslo, USIT
m: +47 45 86 38 50
t: +47 22 85 29 39