Message-ID: <46A9A524.7000009@usit.uio.no>
Date: Fri, 27 Jul 2007 09:56:20 +0200
From: Tommy Gildseth <tommy.gildseth@usit.uio.no>
User-Agent: Thunderbird 2.0.0.0 (X11/20070326)
MIME-Version: 1.0
To: Stefan Zweig <stefanzweig1881@web.de>
CC: pgsql-sql@postgresql.org
Subject: Re: strange result for select * from table where id=random()*900
References: <156384696@web.de>
In-Reply-To: <156384696@web.de>
Content-Type: text/plain; charset=ISO-8859-15; format=flowed
Content-Transfer-Encoding: 7bit

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