I would like to select a random sample of the rows returned by my query. I have read the answer How to get records randomly from the oracle database? but I cannot get the dbms_random.random to work, so I tried with the sample function.
But I am getting an error when I request a sample of my values. A reproducible example would be: The example queries provided are just for reproducability, they have nothing to do with my actual usecase (as in I do not want to select 7 rows or something similar)
select * from (select 1,2,3,4,5,6,7 from dual) sample(0.1)
-- doesn't work
select * from (select 1,2,3,4,5,6,7 from dual)t sample(0.1)
-- doesn't work either
What am I doing wrong here? My goal is to select a random sample of the results of the subquery but I do not have privileges to use dbms_random.value.
The basic function VALUE from DBMS_RANDOM gets a random number, greater than or equal to 0 and less than 1, with 38 digits to the right of the decimal (38-digit precision).
Alternatively, when specify low and high limits, it retrieves a random number between. In your case, you can get a random integer value between 1 and 7 using
select round(dbms_random.value(1,7),0) from dual ;
You can get those values even from your table
select round(dbms_random.value(a.min_value,a.max_value),0) from my_table a ;
As you did not explain what exactly you want to select from your table, I can just provide you with a generic answer.