MySQL RAND() function

Last update on April 04 2022 13:15:36 (UTC/GMT +8 hours)

RAND() function

MySQL RAND() returns a random floating-point value between the range 0 to 1. When a fixed integer value is passed as an argument, the value is treated as a seed value and as a result, a repeatable sequence of column values will be returned.

Syntax:

RAND(), RAND(M);

Argument:

Name	Description
M	A number between 0 to 1.

Pictorial presentation of MySQL RAND() function

pictorial presentation of MySQL RAND() function

Example of MySQL RAND() function

Code:

SELECT RAND();

Explanation:

The above MySQL statement will return a random value between 0 and 1.

Sample Output:

mysql> SELECT RAND();
+-------------------+
| RAND()            |
+-------------------+
| 0.369500624360052 | 
+-------------------+
1 row in set (0.00 sec)

Example: RAND() function with seed value

Code:

SELECT RAND(),RAND(2),RAND(2);

Explanation:

The above MySQL statement will return a random value (between 0 and 1) and the repeatable value using seed in the argument.

Sample Output:

mysql> SELECT RAND(),RAND(2),RAND(2);
+-------------------+-------------------+-------------------+
| RAND()            | RAND(2)           | RAND(2)           |
+-------------------+-------------------+-------------------+
| 0.964232316207357 | 0.655586646549019 | 0.655586646549019 | 
+-------------------+-------------------+-------------------+
1 row in set (0.00 sec)

Example: RAND() function using table

Code:

SELECT FLOOR(RAND()*10)
FROM category;

Explanation:

The above MySQL statement will return the largest integer value after multiply the randomly generated number by 10 but not greater than the generated number.

Sample Output:

mysql> SELECT FLOOR(RAND()*10)
    ->   FROM category; 
+------------------+
| FLOOR(RAND()*10) |
+------------------+
|                7 | 
|                6 | 
|                2 | 
|                0 | 
|                6 | 
+------------------+
5 rows in set (0.29 sec)

Example: RAND() function with where clause

Code:

SELECT pub_name,country,no_of_branch,
   FLOOR(RAND(2)*20)
      FROM publisher
WHERE no_of_branch>FLOOR(RAND(2)*20);

Explanation:

The above MySQL statement will return those rows from publisher table which have no_of_branchs are more than the greatest number after generating the random number with FLOOR function.

Sample Output:

mysql> SELECT pub_name,country,no_of_branch,
    ->    FLOOR(RAND(2)*20)
    ->       FROM publisher
    ->     WHERE no_of_branch>FLOOR(RAND(2)*20);
+--------------------------+---------+--------------+-------------------+
| pub_name                 | country | no_of_branch | FLOOR(RAND(2)*20) |
+--------------------------+---------+--------------+-------------------+
| Jex Max Publication      | USA     |           15 |                13 | 
| BPP Publication          | India   |           10 |                 2 | 
| Mountain Publication     | USA     |           25 |                12 | 
| Summer Night Publication | USA     |           10 |                17 | 
| Novel Publisher Ltd.     | India   |           10 |                 7 | 
+--------------------------+---------+--------------+-------------------+
5 rows in set (0.13 sec)

PHP script:

<!doctype html>
<html lang="en">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
<title>example-rand-function - php mysql examples | w3resource</title>
<meta name="description" content="example-rand-function - php mysql examples | w3resource">
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css">
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<h2>A list of Publishers, their country and no of branches of that publishing house, where no of branches are more than
the random number generated by using the FLOOR function:</h2>
<table class='table table-bordered'>
<tr>
<th>Publishers name</th><th>Country</th><th>Number of branches</th><th>FLOOR(RAND(2)*20)</th>
</tr>
<?php
$hostname="your_hostname";
$username="your_username"; 
$password="your_password";
$db = "your_dbname";
$dbh = new PDO("mysql:host=$hostname;dbname=$db", $username, $password);
foreach($dbh->query('SELECT pub_name,country,no_of_branch,FLOOR(RAND(2)*20) 
           FROM publisher  
           WHERE no_of_branch>FLOOR(RAND(2)*20)') as $row) {
echo "<tr>";
echo "<td>" . $row['pub_name'] . "</td>";
echo "<td>" . $row['country'] . "</td>";
echo "<td>" . $row['no_of_branch'] . "</td>";
echo "<td>" . $row['FLOOR(RAND(2)*20)'] . "</td>";
echo "</tr>";
}
?>
</tbody></table>
</div>
</div>
</div>
</body>
</html>

View the example in browser

Example : RAND() function using order by

Code:

 SELECT *  FROM category 
ORDER BY RAND();

Explanation:

The above MySQL statement will return rows from category table by a random order.

Sample Output:

 mysql> SELECT *  FROM category 
    -> ORDER BY RAND();
+---------+--------------+
| cate_id | cate_descrip |
+---------+--------------+
| CA001   | Science      | 
| CA005   | Medical      | 
| CA003   | Computers    | 
| CA004   | Nature       | 
| CA002   | Technology   | 
+---------+--------------+
5 rows in set (0.05 sec)

PHP script:/strong>

<!doctype html> <html lang="en"> <head> <meta charset="utf-8"> <meta name="viewport" content="width=device-width, initial-scale=1"> <title>example-rand-function - php mysql examples | w3resource</title> <meta name="description" content="example-rand-function - php mysql examples | w3resource"> <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css"> </head> <body> <div class="container"> <div class="row"> <div class="col-md-12"> <h2>A list of categories and their descriptions in a random order:</h2> <table class='table table-bordered'> <tr> <th>Category id</th><th>Description</th> </tr> <?php $hostname="your_hostname"; $username="your_username"; $password="your_password"; $db = "your_dbname"; $dbh = new PDO("mysql:host=$hostname;dbname=$db", $username, $password); foreach($dbh->query('SELECT * FROM category ORDER BY RAND()') as $row) { echo "<tr>"; echo "<td>" . $row['cate_id'] . "</td>"; echo "<td>" . $row['cate_descrip'] . "</td>"; echo "</tr>"; } ?> </tbody></table> </div> </div> </div> </body> </html>

View the example in browser

Online Practice Editor:

All Mathematical Functions

Previous: RADIANS()
Next: ROUND()