# MySQL RAND() function

## 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

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">
<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">
<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";
\$db = "your_dbname";
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

``````<!doctype html>
<html lang="en">
<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">
<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";
\$db = "your_dbname";
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

All Mathematical Functions