To get random number in sql we use rand() function. But rand() funcition returns decimally random between 0 and 1. If we want to get between 1 and 1000 we must multiply the number with top value. Then we must round the number. To round a number we can use 3 different methods. The first one floor method rounds the number to the integer floor value. The second one ceiling value, roundes to ceiling and the third one round method.
Example 1 : Show random number between 0 and 1000;
|
1 |
Select floor(rand()*1001)--If we want the 1000 number to be selectable we must write 1001 |
Example 2 : Show random number between 1 and 1000;
|
1 |
Select ceiling(rand()*1000)--0 is never selected because we use "ceiling" function |
Method-2
|
1 |
Select floor(rand()*1000)+1 --0 is Floor method rounds to floor. So Iff we add 1 It returns between 1 and 1000 |
Example 3 : Show random number between 500 and 1000;
|
1 |
Select floor(rand()*500) + 500 |
Example 4 : Show random number between 200 and 1000;
|
1 |
Select floor(rand()*800) + 200 -- 1000 - 20 = 800 so we multiply with 800, then we add 200 |
Also you can see the related posts below….
Sql random number between 1 and 100
