Random numbers with predictable starting and ending values eg 1 – 10, 1 – 100, 1 -1000
In SQL we use the RND( ) function. Inside the round brackets goes the seed value (an integer value)
Generating a random number requires a few steps.
A) Generating a random number without seed within the brackets generates a unique number between zero and one but the random number is the same for every record in the table
SELECT RND( )
FROM tblsensors;
SQL RND random number without a random number seed
B) Generating a random number with a unique number seed within the brackets generates a unique number between zero and one and the random number is different for every record in the table.
SELECT RND(sensorID )
FROM tblsensors;
With a unique random number seed
C) Then you need to multiple it by 10, 100 or 1000 to get a real value bigger than one.
SELECT RND(sensorID ) * 100
FROM tblsensors;
Multiple to get a number bigger than one
D) Then you need INT to truncate the real number to a whole integer number.
SELECT INT( RND( sensorID) * 100)
FROM tblsensors;
INT the real number to get rid of the decimals
E) Then you need to add one to avoid generating a random number of zero.
SELECT INT( RND( sensorID) * 100) + 1 AS [Random Number]
FROM tblsensors;
Random numbers: From one arbitrary value to another arbitrary value.
Here is the general formula to use.
INT((Upper_Bound – Lower_Bound + 1) * RND(ID) + Lower_Bound)
To generate numbers between interval e.g. between 32 and 78
SELECT INT((78 – 32 + 1) * RND([riderID]) + 32) AS [Random number]
FROM tblRiders;