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">
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<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>
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.0">
<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>
Online Practice Editor:
All Mathematical Functions
- Weekly Trends
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
- JavaScript functions Exercises
- Python Tutorial
- Python Array Exercises
- SQL Cross Join
- C# Sharp Array Exercises
We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook