MySQL RAND()

In SQL the RAND() function allows you to select a random number with a decimal point, between 0 and 1. The result of this function will be different each time the function is executed in an SQL query.

This function comes in handy with an ORDER BY to sort results randomly. However, even if it is possible, you should try to avoid this method as it is not particularly efficient in terms of performance.

The function can also be used in a WHERE clause. This can be useful to select a result randomly.
 

 

Syntax:

The syntax for using the RAND() function is as follows:

SELECT RAND();

The result of this function will be different each time the query is executed. Here is the random results of this function:

0.3256842207966699
0.987036711146835461
0.1298416777661256

It is possible to perform mathematical operations with the result of this function. For example, to get a random number between 0 and 100, just multiply the result of RAND() by 100, as in the example below:

SELECT RAND() * 100;

Output:

15.63986427391230
90.96485569831132
68.630271494701278

 

 

Example :

Let’s assume an application that uses a database of users. In this tutorial, we will use a table that contains 4 users.
 
Users Table:

+------------+-----------+--------+-------------------------------+
|   UserID   |    Name   |  age   |             Address           |
+------------+-----------+--------+-------------------------------+
|     1      |    Alex   |   25   | 819 Saint Francis Way         |
|     2      |   Emily   |   15   | 171 Jarvisville Road Michigan |
|     3      |   Jean    |   35   | 188 Clay Street Indiana       |
|     4      |    Bob    |   40   | 285 Java Lane Missouri        |
+------------+-----------+--------+-------------------------------+

 

Sort the results randomly

It is possible to sort the results randomly using the following syntax:

SELECT *
FROM `Users`
ORDER BY RAND()

Each time the query is executed, it will return a random result. One of the possible results will be the following:

+------------+-----------+--------+-------------------------------+
|   UserID   |    Name   |  age   |             Address           |
+------------+-----------+--------+-------------------------------+
|     3      |   Jean    |   35   | 188 Clay Street Indiana       |
|     1      |    Alex   |   25   | 819 Saint Francis Way         |
|     4      |    Bob    |   40   | 285 Java Lane Missouri        |
|     2      |   Emily   |   15   | 171 Jarvisville Road Michigan |
+------------+-----------+--------+-------------------------------+

 

 

Select a random result

By coupling this SQL function with ROUND() function which allows to round a number to an integer, it is possible to return an integer rather than a floating-point number. This integer result can be used to select a row from a set of records. Here is an SQL query using this method:

SELECT *
FROM `Users`
WHERE `UserID` = ROUND( RAND() * 9 ) + 1

 
Explanation

• RAND() * 9 allows to generate a number between 0 and 9
• ROUND() allows to round the generated number to get an integer numbers
• + 1 adds 1 to the previous result. Thus, the number will be between 1 and 10

This query will therefore use an integer between 1 and 10 to select a user randomly in the “Users” table. One of the possible results is therefore the following:

+------------+-----------+--------+-------------------------------+
|   UserID   |    Name   |  age   |             Address           |
+------------+-----------+--------+-------------------------------+
|     2      |   Emily   |   15   | 171 Jarvisville Road Michigan |
+------------+-----------+--------+-------------------------------+

Be careful with this method if there is no identifier matched no result will be returned.
MCQPractice competitive and technical Multiple Choice Questions and Answers (MCQs) with simple and logical explanations to prepare for tests and interviews.Read More