DRANDOM/DRAND Functions

Description

The DRANDOM/DRAND function returns a random double-precision floating point value in the range of between 0.0 and 1.0. A seed argument that is INTEGER type can be specified. It rounds up real numbers and an error is returned when it exceeds the range of INTEGER.

The DRAND function performs the operation only once to produce only one random number regardless of the number of rows where the operation is output, but the DRANDOM function performs the operation every time the statement is repeated to produce a different random value for each row. Therefore, to output rows in a random order, you must use the DRANDOM function in the ORDER BY clause.

To obtain a random integer value, use the RANDOM/RAND Functions.

Syntax

DRANDOM( [seed] )

DRAND( [seed] )

Example

SELECT DRAND(), DRAND(1), DRAND(1.4);

                   drand()                  drand(1)                drand(1.4)

==============================================================================

    2.849646518006921e-001    4.163034446537495e-002    4.163034446537495e-002

 

SELECT * FROM rand_tbl;

           id  name

===================================

            1  'a'

            2  'b'

            3  'c'

            4  'd'

            5  'e'

            6  'f'

            7  'g'

            8  'h'

            9  'i'

           10  'j'

 

--drandom() returns random values on every row

SELECT DRAND(), DRANDOM() FROM rand_tbl;

   drand()                 drandom()

==============================================================================

   7.638782921842098e-001    1.018707846308786e-001

   7.638782921842098e-001    3.191320535905026e-001

   7.638782921842098e-001    3.461714529862361e-001

   7.638782921842098e-001    6.791894283883175e-001

   7.638782921842098e-001    4.533829767754143e-001

   7.638782921842098e-001    1.714224677266762e-001

   7.638782921842098e-001    1.698049867244484e-001

   7.638782921842098e-001    4.507583849604786e-002

   7.638782921842098e-001    5.279091769157994e-001

   7.638782921842098e-001    7.021088290047914e-001

 

--selecting rows in random order

SELECT * FROM rand_tbl ORDER BY DRANDOM();

           id  name

===================================

            6  'f'

            2  'b'

            7  'g'

            8  'h'

            1  'a'

            4  'd'

           10  'j'

            9  'i'

            5  'e'

            3  'c'