Search Postgresql Archives

random row from a subset

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 




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?


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux