RANDOM/RAND Functions

Description

The RANDOM/RAND function returns any integer value between 0^231 and 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 RAND 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 RANDOM 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 RANDOM function.

To obtain a random real number, use the DRANDOM/DRAND Functions.

Syntax

RANDOM( [seed] )

RAND( [seed] )

Example

SELECT RAND(), RAND(1), RAND(1.4);

       rand()      rand(1)    rand(1.4)

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

   1526981144     89400484     89400484

 

--creating a new table

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'

 

--random() returns random values on every row

SELECT RAND(),RANDOM() FROM rand_tbl;

       rand()       random()

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

   2078876566     1753698891

   2078876566     1508854032

   2078876566      625052132

   2078876566      279624236

   2078876566     1449981446

   2078876566     1360529082

   2078876566     1563510619

   2078876566     1598680194

   2078876566     1160177096

   2078876566     2075234419

 

 

--selecting rows in random order

SELECT * FROM rand_tbl ORDER BY RANDOM();

           id  name

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

            6  'f'

            1  'a'

            5  'e'

            4  'd'

            2  'b'

            7  'g'

           10  'j'

            9  'i'

            3  'c'

            8  'h'