Get random number between 65 to 122

Question

I want to get random number between 65 to 122.I am using this query

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

after getting help from this question but it is giving me numbers outside the range of 65-122.
Please help.Thanks

Already we have solution at http://stackoverflow.com/questions/14798640/creating-a-random-number-using-mysql — Karthikeyan Sukkoor, Dec 23 '13 at 13:58

score 3 · Answer 1 · answered Dec 23 '13 at 13:55

3

I think what you want is

65 + FLOOR( RAND() * 57 )

a minimum of 65 PLUS random of 0-57, so at the high end, 65 + 57 = 122

answered Dec 23 '13 at 13:55

DRapp

47,638
12
72
142

score 3 · Accepted Answer · answered Dec 23 '13 at 13:56

3

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

answered Dec 23 '13 at 13:56

PeterRing

1,767
12
20

h2ooooooo · Answer 3 · 2013-12-23T14:06:30.510

RAND() returns a random number between 0 and 1, hence 65 + RAND() * 122 is equal to minimum/maximum:

MIN: 65 + 0 * 122 = 65 + (0 * 122) = 65 + 0 = 65
MAX: 65 + 1 * 122 = 65 + (1 * 122) = 65 + 122 = 187

Hence what you actually want 57 instead of 122 as 122 - 65 = 57.

Now the minimum/maximum is:

MIN: 65 + 0 * 57 = 65 + (0 * 57) = 65 + 0 = 65
MAX: 65 + 1 * 57 = 65 + (1 * 57) = 65 + 57 = 122

Alternately, here's a custom function you can use:

DELIMITER //

CREATE FUNCTION RAND_BETWEEN(min FLOAT, max FLOAT) 
    RETURNS FLOAT
    BEGIN
        RETURN min + ((max - min) * RAND());
    END//

DELIMITER ;

Usage:

SELECT RAND_BETWEEN(60, 120)        # eg. 115.81029510498047
SELECT RAND_BETWEEN(0, 150)         # eg. 62.729270935058594
SELECT RAND_BETWEEN(10000, 10005)   # eg. 10004.9560546875
SELECT FLOOR( RAND_BETWEEN(5, 10) ) # eg. 6

score 1 · Answer 4 · answered Dec 23 '13 at 13:59

1

A typical way to write this is:

select 65 + FLOOR( RAND() * (122 - 65))

Writing it this way allows you to easily see the extreme values.

By the way, your question is a bit unclear as to whether 122 is included. The formula would be slightly different depending on that.

answered Dec 23 '13 at 13:59

Gordon Linoff

1,242,037
58
646
786

+1 and this one should be the accepted anwser.. because this one explains the most simple calculation behind the lower and upper bound – Raymond Nijland Dec 23 '13 at 16:54

Get random number between 65 to 122

4 Answers4