random row from a subset

From: Peter Koukoulis <pkoukoulis(at)gmail(dot)com>
To: "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org>
Subject: random row from a subset
Date: 2017-09-20 00:33:08
Message-ID: CABpxA9iaYJDR1D_hn3Bb+qXMmZ-Cf-JaGb677k788mPwg4SYSQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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?

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Stephen Cook 2017-09-20 01:37:39 Re: pgcrypto encrypt
Previous Message Andres Freund 2017-09-19 23:50:11 Re: Up to date conventional wisdom re max shared_buffer size?