random row from a subset
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);
c_id
------
2047
(1 row)
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);
c_id
------
(0 rows)
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);
c_id
------
1298
2608
(2 rows)
But in Oracle when I run the same query, I observe a consistent randomly
selected customer id, which is what I expecting:
SQL> 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=:d_id
)
where
rn = (select floor(dbms_random.value(1,max_rn+1)) from dual) 2 3 4
5 6 7 8 ;
C_ID
----------
2938
SQL> 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=:d_id
)
where
rn = (select floor(dbms_random.value(1,max_rn+1)) from dual) 2 3 4
5 6 7 8
9 ;
C_ID
----------
2204
SQL> 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=:d_id
)
where
rn = (select floor(dbms_random.value(1,max_rn+1)) from dual) 2 3 4
5 6 7 8
9 ;
C_ID
----------
2265
Can somebody help with formulating a SQL statement that would behave as how
the existing SQL statement does in Oracle, but not PostgreSQL?
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
thanks, interestingly your method works in both Oracle and PostgreSQL,
albeit with a different random function call.
It does not work in SQL Anywhere though.
--
Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
On 09/20/2017 01:28 PM, bluefrog wrote:
thanks, interestingly your method works in both Oracle and PostgreSQL,
albeit with a different random function call.
It does not work in SQL Anywhere though.
You will have to ask SQL Anywhere people, I guess.
cheers
--
Tomas Vondra http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general