Re: strange result for select * from table where id=random()*900 - Mailing list pgsql-sql

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
notwork.
 
> 
> actually i get sometime zero, sometimes one, sometimes two and sometimes even three results back from the above query
althoughi 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