Select exact number of rows from dual table

Question

The task is the following: select 20 rows from dual table with randomly generated distinct numbers from 23 to 45.

I performed the following:

select distinct floor(dbms_random.value(23,45)) output
from   dual
connect by rownum <= 20;

But it selects random number of rows less than 20. For example:

     OUTPUT
        44
        35
        25
        27
        40
        32
        26
        36
        43
        34
        31
        33
        37
13 rows selected.

Please help, how to select exactly 20 numbers, not less? Lot of thanks in advance!

Answer 1

Use a row generator to generate all the numbers; order them randomly using DBMS_RANDOM.VALUE and then get the first 20 rows:

SELECT OUTPUT
FROM   (
  SELECT 22 + LEVEL AS OUTPUT
  FROM   DUAL
  CONNECT BY 22 + LEVEL <= 45
  ORDER BY DBMS_RANDOM.VALUE
)
WHERE ROWNUM <= 20

Why your code does not work:

The code you are using may randomly generate 20 distinct numbers but it is highly likely that it will not as it will generate 20 rows of random integers between 23 and 45 and then the DISTINCT clause will remove all the duplicates and you are likely to have duplicates which will reduce the final number of rows below 20.

Mathematically, the first row it generates will be unique then there is a 22-in-23 chance the second row is unique and, given the previous rows are unique, a 21-in-23 chance the 3rd row is unique and ... a 4-in-23 chance the 20th row is unique. Multiplying all those probabilities together:

SELECT probabilities ( number_of_rows, probability ) AS (
  SELECT 1, 1 FROM DUAL
UNION ALL
  SELECT number_of_rows + 1, probability * ( 23 - number_of_rows ) / 23
  FROM   probabilities
  WHERE  number_of_rows < 20
)
SELECT * FROM probabilities;

Gives a probability of 0.0000025 that you will generate all 20 rows with your method - possible but improbable.