using rand over a subset of rows

[Former Member]

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 successfully and get a different randomly selected 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:

$ dbisql -c "uid=sadb;pwd=sadb;eng=ftnode_sa;links=tcpip(host=10.11.12.17;port=49153);LOG=conn.log;dbn=ftnode_sa" "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(rand()*(max_rn))+1)"
c_id 
----
1308

(1 rows)

$ dbisql -c "uid=sadb;pwd=sadb;eng=ftnode_sa;links=tcpip(host=10.11.12.17;port=49153);LOG=conn.log;dbn=ftnode_sa" "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(rand()*(max_rn))+1)"
c_id 
----

(0 rows)

$ dbisql -c "uid=sadb;pwd=sadb;eng=ftnode_sa;links=tcpip(host=10.11.12.17;port=49153);LOG=conn.log;dbn=ftnode_sa" "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(rand()*(max_rn))+1)"
c_id 
----
1854 
2600 

(2 rows)

and this is the Oracle behaviour:

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?

[Former Member]

Just thought I'd post a quick update. The query in my first post does not work in PostgreSQL either, works fine in Oracle however.

The following query works in PostgreSQL and Oracle however:

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

3016
(1 row)

albeit with different function calls. The difference being the inclusion of a WITH subquery.
The same method however still does not work in SQL Anywhere.

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

[VolkerBarth · Accepted solution]

I'm not sure whether RAND() is reliable in subqueries or CTEs, cf. that unanswered FAQ...