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.
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
round() will give a non-uniform distribution.RAND(), UUID(), NOW() are indeterministic functions. The calling of such functions should be avoid from being written into the bin log for replication. For example. INSERT INTO t SET ID=UUID(); will cause the value of the ID fields to be different on master and slaves. Instead it needs to be written as SET @uuid:=UUID();, and then INSERT INTO t SET ID=@uuid;, then run them in a single transaction. This will be replication safe. This is a bit off topic for this question. It doesn't say your answer has any problem. :)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
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
these both are working nicely:
select round(<maxNumber>*rand())
FLOOR(RAND() * (<max> - <min> + 1)) + <min> // generates a number
between <min> and <max> inclusive.
min is 0 and ROUND is doing the work of FLOOR with +1 * [from 0 to less than 1]. And the first dupes this earlier answer (but in a way that doesn't answer the OP) and the second dupes this one.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)...
This is correct formula to find integers from i to j where i <= R <= j
FLOOR(min+RAND()*(max-min))
FLOOR(min+RAND()*(max-min+1))