Re: random row from a subset

On 09/20/2017 02:33 AM, Peter Koukoulis wrote:
>
> I'm attempting to get a random, based on a range that spans 1 to the
> maximum number of rows that for a subset.
> I run the query in Oracle sucessfully and get a different number each
> time and only a single number, which is what I am expecting,
>
> but when I run the same query, albeit the random function is different,
> I either observe no result, a single row or two rows, 
> for example:
>
> ft_node=# select c_id
> from    (
>          select c_id, row_number() over (order by c_d_id) as rn
>               ,  count(*) over() max_rn
>          from customer where c_d_id=5
>         ) t
> where rn = (select floor(random()*(max_rn))+1);

The problem here is that random() is volatile, so it's executed for each
row. So essentially if the subselect has 100 rows, you'll get 100 random
values. So you're "rolling the dice" for every row independently.
Sometimes one row matches, sometime none, sometime more than one.

You need to do either this:

with rand as (select random() as r)
select c_id
from (
select c_id, row_number() over (order by c_d_id) as rn
, count(*) over() max_rn
from customer where c_d_id=5
) t
where rn = (select floor(r*(max_rn))+1 from rand);

or define an immutable wrapper for random():

CREATE FUNCTION random_stable() RETURNS DOUBLE PRECISION
AS 'SELECT random()'
LANGUAGE SQL
IMMUTABLE;

and use that instead.

regards

--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services