Generate a random number in the range 1 - 10
If by numbers between 1 and 10 you mean any float that is >= 1 and < 10, then it's easy:
select random() * 9 + 1
This can be easily tested with:
# select min(i), max(i) from ( select random() * 9 + 1 as i from generate_series(1,1000000)) q; min | max-----------------+------------------ 1.0000083274208 | 9.99999571684748(1 row)
If you want integers, that are >= 1 and < 10, then it's simple:
select trunc(random() * 9 + 1)
And again, simple test:
# select min(i), max(i) from ( select trunc(random() * 9 + 1) as i from generate_series(1,1000000)) q; min | max-----+----- 1 | 9(1 row)
To summarize and a bit simplify, you can use:
-- 0 - 9select floor(random() * 10);-- 0 - 10SELECT floor(random() * (10 + 1));-- 1 - 10SELECT ceil(random() * 10);
And you can test this like mentioned by @user80168
-- 0 - 9SELECT min(i), max(i) FROM (SELECT floor(random() * 10) AS i FROM generate_series(0, 100000)) q;-- 0 - 10SELECT min(i), max(i) FROM (SELECT floor(random() * (10 + 1)) AS i FROM generate_series(0, 100000)) q;-- 1 - 10SELECT min(i), max(i) FROM (SELECT ceil(random() * 10) AS i FROM generate_series(0, 100000)) q;
If you are using SQL Server then correct way to get integer is
SELECT Cast(RAND()*(b-a)+a as int);
Where
- 'b' is the upper limit
- 'a' is lower limit