How can I do to get a random number between two values?
Between a mínimo
and a máximo
, for example, a number between 1
and10
Try doing a SELECT
and using RAND()
:
SELECT RAND() as aleatorio
But I don't know how to indicate the range, because the following doesn't work:
SELECT RAND(1, 10) as aleatorio
I get the following error:
#1582 - Incorrect parameter count in the call to native function 'RAND'
If we take into account the maximum and minimum values that the function returns
RAND()
:One solution is to use the following formula:
Decimal result option:
If we want to obtain a number with decimals, then we can do:
This will return a number greater than 1 and less than 10
Integer result option:
If we want to get an integer, then we can use:
FLOOR()
: we will obtain an integer greater than 1 and less than 10 .CEIL()
: we will obtain an integer greater than 1 and less than 10 .ROUND()
: we will obtain an integer greater than 1 and less than 10 .Example:
Observations:
PostgreSQL
does not existrand()
, butrandom()
and the operation is identical.SQLServer
:ROUND
, that isROUND(x, y)
, wherey
is the number of decimal places.CEIL()
, butCEILING
, and it works the same.The function
RAND()
is not a good choice for generating random numbers as it will always return the same number within the query. Which we can check by executing the following code:A better option is to use
NEWID()
to generate random values. To get integers, we just addCHECKSUM()
. So that they are always positive, we addABS()
. To generate a range, we use the modulus with the width of the range and add the initial value to it. Thus, the formula would look like this:Clarification : This solution is for SQL Server. It has not been tested with other RDBMS. It is presented because the question includes 4 different RDBMS.
This solution is for Oracle .
Ref: 108 DBMS_RANDOM
This is the one that worked for me: SELECT FLOOR( i + RAND() * ( f - i + 1 ) );
Example: we will obtain a random value between [5,10] where:
i=5
f=10