CODEWITHSUNDEEP
mysqlsql
TNK
TNK

Reputation: 4333

creating a random number using MYSQL

I would like to know is there a way to select randomly generated number between 100 and 500 along with a select query.

Eg: SELECT name, address, random_number FROM users

I dont have to store this number in db and only to use it to display purpose.

I tried it something like this, but it can't get to work..

SELECT name, address, FLOOR(RAND() * 500) AS random_number FROM users

Hope someone help me out.

Upvotes: 107

Views: 135279

Answers (6)

Ja͢ck
Ja͢ck

Reputation: 173662

This should give what you want:

FLOOR(RAND() * 401) + 100

Generically, FLOOR(RAND() * (<max> - <min> + 1)) + <min> generates a number between <min> and <max> inclusive.

Update

This full statement should work:

SELECT name, address, FLOOR(RAND() * 401) + 100 AS `random_number` 
FROM users

Upvotes: 161

KhalilPan
KhalilPan

Reputation: 65

these both are working nicely:

select round(<maxNumber>*rand())

FLOOR(RAND() * (<max> - <min> + 1)) + <min> // generates a number
between <min> and <max> inclusive.

Upvotes: 4

Kadir Erturk
Kadir Erturk

Reputation: 601

Additional to this answer, create a function like 

CREATE FUNCTION myrandom(
    pmin INTEGER,
    pmax INTEGER
)
RETURNS INTEGER(11)
DETERMINISTIC
NO SQL
SQL SECURITY DEFINER
BEGIN
  RETURN floor(pmin+RAND()*(pmax-pmin));
END;

and call like

SELECT myrandom(100,300);

This gives you random number between 100 and 300

Upvotes: 5

ﻂﺎﻫﺭ ﻏﻔﺎﺮ
ﻂﺎﻫﺭ ﻏﻔﺎﺮ

Reputation: 49

This is correct formula to find integers from i to j where i <= R <= j

FLOOR(min+RAND()*(max-min))

Upvotes: 0

Ed Heal
Ed Heal

Reputation: 60037

As RAND produces a number 0 <= v < 1.0 (see documentation) you need to use ROUND to ensure that you can get the upper bound (500 in this case) and the lower bound (100 in this case)

So to produce the range you need:

SELECT name, address, ROUND(100.0 + 400.0 * RAND()) AS random_number
FROM users

Upvotes: 11

bonCodigo
bonCodigo

Reputation: 14361

You could create a random number using FLOOR(RAND() * n) as randnum (n is an integer), however if you do not need the same random number to be repeated then you will have to somewhat store in a temp table. So you can check it against with where randnum not in (select * from temptable)...

Upvotes: 3

Related Questions