1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

Query to get 6 random numbers

Discussion in 'SQL PL/SQL' started by Bharat, Jun 1, 2012.

  1. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi all,

    The below query gives 6 random numbers.

    Code (Text):

    Query: -

    SQL> SELECT RANDOM_NUMBER
    2 FROM ( SELECT RANDOM_NUMBER
    3        FROM ( SELECT ROWNUM RANDOM_NUMBER
    4               FROM ALL_OBJECTS
    5               WHERE ROWNUM < &range)
    6        ORDER BY DBMS_RANDOM.VALUE )
    7    WHERE ROWNUM <= &how_many;

    Output: - give range as 50 and how_many as 6  to get 6 random numbers in 1 to 50 numbers.

    RANDOM_NUMBER
    ---------------------
           16
           40
           22
           29
           26
           43
     
    Here range parameter defines range of values to get random numbers and how_many variable defines how many random numbers we need in range of numbers.
     
  2. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    The following query will do the trick.

    Code (SQL):
    SQL> SELECT TRUNC(DBMS_RANDOM.VALUE(1,51)) random_numbers FROM dual
      2  CONNECT BY level <=6;

    RANDOM_NUMBERS
    --------------
                13
                13
                42
                16
                36
                38

    6 ROWS selected.

    SQL>
     
  3. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Dear Raj,

    Here from your solution we are getting duplicate values.
     
  4. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    The below query would be the perfect one because we are not using any tables, as we used all_objects in my previous post.


    Code (Text):


    SQL> select * from (select level Random_Number from dual connect by level<=50 order by dbms_random.random) where rownum<6;

    Output: -
    Random_Number
    --------------
        49
        4
        32
        13
        28

     
    And also from the above query we don't get any duplicate values.
     
  5. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Yes, Bharath . it is possible to have duplicate when the query randomly numbers from 1 to 51 and select just 6 of them.

    Anyway the following query will eliminates the chance of duplicates. (It stands close to you qurey).
    Code (SQL):
    SQL> SELECT  * FROM (
      2      SELECT DISTINCT TRUNC(DBMS_RANDOM.VALUE(1,51)) random_numbers
      3      FROM dual CONNECT BY level <=500 ORDER BY DBMS_RANDOM.VALUE )
      4  WHERE rownum <=6 ;

    RANDOM_NUMBERS
    --------------
                44
                16
                 6
                43
                 7
                22

    6 ROWS selected.
     
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Oracle provides the dbms_random package to generate random numbers; it seems you took the long way around to do what dbms_random is written to do -- sort of like re-inventing the wheel -- and yet you also used dbms_random to randomly sort non-random numbers (rownum is a sequential series starting at 1 and extending to the end of the returned data).

    I'm curiious as to why you'd take the effort to do more work to get the same result as the dbms_random package. Also truly random numbers are, well, random, and can repeat:

    Code (SQL):
    SQL> SELECT round(dbms_Random.VALUE(1,61), 0) random FROM article;
     
    RANDOM
    ---------------
    48
    9
    60
    60
    39
    11
    53
    13
    51
    34
     
    10 ROWS selected.
     
    SQL> /
     
    RANDOM
    ---------------
    23
    31
    60
    25
    14
    11
    30
    14
    17
    15
     
    10 ROWS selected.
     
    SQL> /
     
    RANDOM
    ---------------
    53
    50
    36
    24
    52
    33
    23
    45
    58
    25
     
    10 ROWS selected.
     
    SQL>
     
    Notice that in three successive runs only one produced unique results, which is normal behaviour for a random number generator. It is admirable that you took on this task but it appears that you walked around the block only to go next door.
     
  7. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi David,

    I have used following query to generate random numbers without repetition of any number twice. Is there any drawback in my query.

    Code (Text):


    SQL> select * from (select level Random_Number from dual connect by level<=50 order by dbms_random.random) where rownum<6;

    Output: -
    RANDOM_NUMBER
    --------------
    4
    20
    49
    28
    22

    5 rows selected

    SQL>/
    RANDOM_NUMBER
    --------------
    36
    29
    41
    6
    9

    5 rows selected


    SQL>/
    RANDOM_NUMBER
    --------------
    27
    33
    15
    50
    48


    5 rows selected


     

    Note: - I think random numbers means which doesn't generate same numbers in set of values.

    Already in previous post RAJ mentioned same query with truncating the values. Here when we use trunc or round we can get duplicates in some cases.

    Example: - When we use this random numbers concept to generate random questions in online test we are not supposed to generate same questions twice or thrice. We need to skip the number which is already generated. So I given a try with that query.
     
  8. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Random numbers are RANDOM meaning there is no rhyme nor reason to their generation. Duplicates DO occur in random number sequences -- see my prior response generating truly random numbers. You are taking a sequence and randomly ordering it, which is not generating random numbers. ROWNUM is a sequential value starting with 1 and ending with the last record in the result set; absolutely no randomness there and ordering those results by using a true random number generator doesn't make them random. [The order by clause filters the random numbers and discards duplicates to provide a unique ordering.] DBMS_RANDOM is provided to generate random numbers; you're using it to generate the ordering of your results when you should be using the package procediures to generate actual random numbers. Random selection is not the same as generating random numbers; randomly selecting 50 questions from a pool of 200 uses a pseudo-random number generator, one which generates random numbers with the condition that no two be repeated.

    Your query will successfully select X number of questions in a somewhat 'random' fashion (no two alike and in a haphazard sequence) but it doesn't generate truly random numbers. Look at your process flow --

    1 Generate a random integer
    2 Determine if that integer has been selected before in this set
    3 Discard numbers generated twice
    4 Add the question to the exam
    5 Return to step 1 until the total number of questions is selected

    This is how a pseudo-random number generator functions. Truly random integers can repeat as illustrated by my prior response; discarding duplicates increases the number of random numbers generated to ensure no duplication in the final result set but takes away one aspect of randomness, namely the possibility of duplicate values in the generated results.
     
    Bharat and Sadik like this.
  9. Bharat

    Bharat Community Moderator Forum Guru

    Messages:
    1,747
    Likes Received:
    147
    Trophy Points:
    1,805
    Location:
    Vijayawada, India
    Hi David,

    I want to generate 'N' unique numbers randomly. If I use the query what you posted before then Iam getting duplicates. For this problem what should I need to do.
     
  10. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    .

    It reminds me a situation where we used to generate voucher PIN Number to recharge the prepaid mobile numbers previously. You can do the following.

    1. Create a table with the number field being Primary Key and a flag field to indicate whether the number has been used before.
    2. Genarate the desired set of numbers using dbms_Random package.
    3. Populatre the table by handling the dupliate exception.
    4. Select the desire numbers from table. Chnage the Flag of number when selected.

    In Telecom domain the process was more complicated.