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!

Using Random number in the where clause

Discussion in 'SQL PL/SQL' started by ay_dj, Oct 12, 2011.

  1. ay_dj

    ay_dj Active Member

    Messages:
    1
    Likes Received:
    0
    Trophy Points:
    75
    If you want a random number in the where clause, Use a hash

    Code (SQL):
    SELECT * FROM emp WHERE dbms_utility.get_hash_value(dump(rowid),0,100)<10;
     
  2. tj.abrahamsen

    tj.abrahamsen Active Member

    Messages:
    10
    Likes Received:
    1
    Trophy Points:
    90
    Location:
    Utah
    Just in case someone find this post.

    You can use DBMS_RANDOM.VALUE as well. Like so:

    Code (SQL):
    SELECT  *
    FROM    wr_result wr
    WHERE   wr.rank_level = TRUNC(DBMS_RANDOM.VALUE(1, 10))
    ;
     
    Format: DBMS_RANDOM.VALUE(<min value>, <max value>)

    ~ TJ