MySQL Get a Random Value Between Two Values

MySQL get a random value between two values

Actually, ROUND((RAND() * (max-min))+min) is the best way in MySQL to do what you'd like. It is also the best way in ActionScript, JavaScript, and Python. Honestly, I prefer it to the PHP way because it is more convenient.

Because I don't know how many rows you'll be returning, I can't advise you whether it is better to use PHP or MySQL for this, but if you're dealing with a large number of values you probably are better off using MySQL.

Addendum

---

So, there was a question as to whether this is better in PHP or MySQL. Instead of getting into a debate on principles, I ran the following:

<pre><?php

$c = mysql_connect('localhost', 'root', '');

if(!$c) die('!');
echo mysql_select_db('test', $c)?'Connection':'Failure';
echo PHP_EOL;

echo ':::::::::::::::::::::::::BEGINNING MYSQL RAND::::::::::::::::::::::::::::::'.PHP_EOL;
$start = microtime(1);
for( $i = 0; $i < 100000; $i++ )
{
    $r = mysql_query( 'SELECT ROUND(RAND() * (200-10) + 10) FROM dual' );
    $r = mysql_fetch_array( $r );
}
$end = microtime(1);

echo  ($end - $start) . " for MySQL select".PHP_EOL;

echo ':::::::::::::::::::::::::BEGINNING PHP RAND::::::::::::::::::::::::::::::' .PHP_EOL;
$start = microtime(1);
for( $i = 0; $i < 100000; $i++ )
{
    $r = mysql_query( 'SELECT 200 AS two, 10 AS tem FROM dual' );
    $r = mysql_fetch_array( $r );
    $r[2]= rand($r[0], $r[1]);
}
$end = microtime(1);

echo  ($end - $start) . " for PHP select".PHP_EOL;

MySQL is faster by about 2-3%.

If you use this, however (note, more columns return by MySQL):

<pre><?php

$c = mysql_connect('localhost', 'root', '');

if(!$c) die('!');
echo mysql_select_db('test', $c)?'Connection':'Failure';
echo PHP_EOL;

echo ':::::::::::::::::::::::::BEGINNING MYSQL RAND::::::::::::::::::::::::::::::'.PHP_EOL;
$start = microtime(1);
for( $i = 0; $i < 100000; $i++ )
{
    $r = mysql_query( 'SELECT ROUND(RAND() * (200-10) + 10) as rd, 200 as two, 10 as ten FROM dual' );
    $r = mysql_fetch_array( $r );
}
$end = microtime(1);

echo  ($end - $start) . " for MySQL select".PHP_EOL;

echo ':::::::::::::::::::::::::BEGINNING PHP RAND::::::::::::::::::::::::::::::' .PHP_EOL;
$start = microtime(1);
for( $i = 0; $i < 100000; $i++ )
{
    $r = mysql_query( 'SELECT 200 AS two, 10 AS tem FROM dual' );
    $r = mysql_fetch_array( $r );
    $r[2]= rand($r[0], $r[1]);
}
$end = microtime(1);

echo  ($end - $start) . " for PHP select".PHP_EOL;

MySQL comes out behind by 3-4% (very inconsistent results) (about the same results if you don't use an array index assignment for $r[2]).

The major difference, it seems, comes from the number of records return to PHP and not the randomization system itself. So, if you need column A, column B, and a random value, use PHP. If you only need the random value, then use MySQL.

How do you generate multiple unique random numbers within a range in mysql

Welcome to S/O. So, RAND() returns a random floating point number between 0 and 1, such as .28932353289090. So, if you want to get a given value such as from 0-10, just multiply the RAND() * 10. It will still be floating point, so if you wanted an integer of it, just wrap that with a cast, such as cast( rand() * 10 as signed ) to get an INTEGER value result. But casting this can give you a 10 due to implied rounding such as getting a .95 or greater will round UP to the next digit.

Next, you want the breakdowns going for the low, medium and high range of 2 - 32, 33-65, 66-98. So your gaps are not perfectly balanced, but I think what you are trying to do is break a possible 0-98 (99 possible values) into 3 groups. Why those specific, I dont know, but can give you a good start to see how you might get and adjust as you see fit.

Your low group is actually at most 32, but starting a MINIMUM of 2. So, start with your maximum RANGE which is 30. So, based on the casting I exampled above,

cast( 30 * rand() as signed) 

will give you a value of 0-30. Now, just add 2 to it since that is your low range, thus a final of

2 + cast( 30 * rand() as signed)

Now, apply similar logic to your medium and high.

33-65 = range = 33-33 = 0 (low), 65-33 = 32 (high) randomness + a fixed 33

33 + cast( 32 * rand() as signed) 

So, if the rand() return 0, then you are at 33 + 0 = 33, your low value for the mid range. If the rand() returns the .95 or better, * 32 will give you your high randomness of 32 + your base of 33 = 65, the high end of you mid range.

Same for your high end

66-98 = 66-66 = 0 (low), 98-66 = 32 (high) + fixed 66.

66 + cast( 32 * rand() as signed) 

Finally, your steps between x, where x = 1, you are in the below 100 range. x=2 is 100 starting range, x=3 = 200 starting range. So this is simply tacking on (x-1) * 100 to whatever your underlying random such as

(( x-1 ) * 100 ) + ( 33 + cast( 32 * rand() as signed ))

So, you were getting close, but I think this resolves it for you. By using FLOOR() will always chop-off the rounding, but applying the cast as signed will allow the full range. Your choice on means to get rid of rounding.

Select random number from a specific list

The following will give you one of the numbers from a list:

SELECT ELT(FLOOR(RAND() * 4) + 1, 10, 20, 30, 50);

Here 4 is the size of list and one is added since ELT is one-based.

Get random number between 65 to 122

SELECT (FLOOR(65 + RAND() * 57));

Rand provides a number between 0-1. Like 0,75.
So if you want to get a number betwen 65 and 122 then:

65 = 65+0*57
122 = 65+1*57

How to get mysql random integer range?

This is working for me. Your mysql version maybe?

SELECT id, (FLOOR( 1 + RAND( ) *60 )) AS timer
FROM users
LIMIT 0 , 30

LIMIT by random number between 1 and 10

Eureka...

In pseudo code:

• execute a query to select 10 random rows
• select from that assigning a row number 0-9 using a user defined variable to calculate that
• cross join with a single hit on rand() to create a number 0-9 and select all rows with row number less than or equal to that number

Here's the essence of the solution (you can adapt your query to work with it:

select * from (
  select *, (@row := coalesce(@row + 1, 0)) row from (
    // your query here, except simply LIMIT 10
    select * from mytable
    order by rand()
    limit 10
  ) x
) y
cross join (select rand() * 10 rand) z
where row <= rand

See SQLFiddle. Run it a few times and you'll see you get 1-10 random rows.

If you don't want to see the row number, you can change the outer select * to select only the specific columns from the inner query that you want in your result.

Can MySQL generate random numbers that can be either negative or positive?

SELECT -1+2*RAND();

Should return a double between -1.0 and 1.0.

---

Related Topics