| 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: | Whole Thread | Raw Message | 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?
| 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? |