Get a random value between two values in MySQL?

To get the random value between two values, use MySQL rand() method with floor(). The syntax is as follows.

select FLOOR( RAND() * (maximumValue-minimumValue) + minimumValue) as anyVariableName;

Let us check with some maximum and minimum value. The maximum value we are considering is 200 and minimum is 100. The random number will be between 100 and 200 including 100 and 200 itself.

The query is as follows.

mysql> select FLOOR( RAND() * (200-100) + 100) as RandomValue;

The following is the output.

+-------------+
| RandomValue |
+-------------+
| 144         |
+-------------+
1 row in set (0.00 sec)

Now if we will run the same query again, the output will differ.

mysql> select FLOOR( RAND() * (200-100) + 100) as RandomValue;

The following is the output with a different value since these are random values between a range we set above.

+-------------+
| RandomValue |
+-------------+
| 184         |
+-------------+
1 row in set (0.00 sec)

Ankith Reddy

Published on 11-Jan-2019 11:08:37

• Related Questions & Answers
• Select a specific value between two column values in MySQL?
• Fetch a value between different values in MySQL
• Select two random rows in a MySQL database?
• Get Random value from a range of numbers in JavaScript?
• Order randomly in MySQL with a random value column?
• Random whole number between two integers JavaScript
• How to get the max of two values MySQL?
• Fix a specific column value and display random values for rest of the rows in MySQL
• Find the difference between two datetime values with MySQL?
• MySQL query to get first two highest column values from a table?
• Get the difference between two timestamps in seconds in MySQL?
• How to find missing value between two MySQL Tables?
• How to get MySQL random integer range?
• How to get the greatest of two columns values in MySQL?
• How to find prime numbers between two values or up to a value in R?