SQL RAND: Generate a Random Number at Runtime

Summary: in this tutorial, you will learn how to use the SQL RAND function to generate a pseudo-random number at runtime.

Introduction to SQL RAND function

The RAND function generates a pseudo-random floating-point number between 0 and 1 (inclusive). The following illustrates the syntax of the RAND number:

RAND(seed);Code language: SQL (Structured Query Language) (sql)

The RAND function accepts an optional seed argument with the integer data type. If you call the RAND function with the same seed value within a session, the function will return exactly the same value. To get different random numbers, you must specify different seed values, or use different sessions.

If you omit the seed argument, the RAND function returns a floating-point random number between the function calls.

Note that some database systems e.g., PostgreSQL, provides a function named RANDOM that is equivalent to the RAND function.

SQL RAND function examples

The following example shows how to generate a random number between 0 and 1.

SELECT RAND();Code language: SQL (Structured Query Language) (sql)

If you execute the statement multiple times, you will see different random numbers.

If you execute the following statement multiple times, it yields exactly the same value because the RAND function uses the same seed value.

SELECT RAND(100);Code language: SQL (Structured Query Language) (sql)

Generate a random integer between a range

To generate a random integer R in the range (n,m), n <= R < m, you use the RAND function in conjunction with the ROUND function as follows:

ROUND(n + RAND() * (m − n))Code language: SQL (Structured Query Language) (sql)

For example, to get a random number between 1 and 100, you use the following statement.

SELECT ROUND(1 + (RAND() * 99)) AS RAND_1_100;Code language: SQL (Structured Query Language) (sql)

Querying rows in random order

You can use the RAND function in the ORDER BY clause to retrieve rows in random order as follows:

SELECT * FROM table_name
ORDER BY RAND();Code language: SQL (Structured Query Language) (sql)

To select a random sample from a set of rows, you add the LIMIT clause to the above statement. The following statement retrieves N random rows in a table.

SELECT * FROM table_name
ORDER BY RAND()
LIMIT N;Code language: SQL (Structured Query Language) (sql)

Let’s see the employees table in the sample database

The following statement retrieves 5 random employees.

SELECT employee_id, first_name, last_name
FROM employees
ORDER BY RAND()
LIMIT 5;Code language: SQL (Structured Query Language) (sql)

 employee_id | first_name | last_name
-------------+------------+-----------
         113 | Luis       | Popp
         204 | Hermann    | Baer
         179 | Charles    | Johnson
         110 | John       | Chen
         111 | Ismael     | Sciarra
(5 rows)Code language: SQL (Structured Query Language) (sql)

Note that you should use this way for getting random rows from a table with a small set of data. For a big table, it would be slow.

In this tutorial, you have learned about the SQL RAND function to generate a pseudo-random number in queries at runtime.

Was this tutorial helpful ?