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?
