I Need to generate a random number between two numbers for each row.
For Eg:
select ColName,(Random nos between 1500 to 2000) from TableName
Thanks in advance
4 Answers
Random number formula is
RANDOM DECIMAL RANGE
SELECT RAND()*(b-a)+a;
Example
SELECT RAND()*(25-10)+10; // Random between 10 to 25
RANDOM INTEGER RANGE
SELECT FLOOR(RAND()*(b-a)+a);
Example
SELECT FLOOR(RAND()*(25-10)+10);
1 Comment
Ullas
You have to use
FLOOR or ROUND or CEILING function for avoiding the decimal part.DECLARE @start INT
DECLARE @end INT
SET @start = 1500
SET @end = 2000
SELECT Round(( ( @end - @start - 1 ) * Rand() + @start ), 0)
Update : To get random number for each row.
select ABS(Checksum(NewID()) % (@end-@start)) + @start
4 Comments
Vignesh M
I need a random number for each row.. this will not generate different values for each row
Vignesh M
a little change required
Pரதீப்
@VigneshM - Check now.
Diego Scaravaggi
NewId() contains some interlocked threaded variables I prefer to use getdate and @@packet_received in order to have random numbers:
rand(@@spid+ @@PACK_RECEIVED + datepart(microsecond,getdate()) )a little change required, by replacing the following code it will respond.
Round(( ( @end - @start - 1 ) * Rand(checksum(newid())) + @start ), 0)
Eg:
Generate Same Value :
select top 10 ROUND(((2000 - 1500 -1) * RAND() + 1500), 0) from sysobjects
Generate Different Value :
select top 10 ROUND(((2000 - 1500 -1) * Rand(checksum(newid()))+ 1500), 0) from sysobjects
Comments
SELECT ROUND(((2000 - 1500 -1) * RAND() + 1500), 0)
