Generate random number between 0.85 and 0.95 for each row in SQL Server-CodePudding

I'd like to generate a random number between 0.85 and 0.95 for each row.

At first I tried using (rand()*(0.85-0.95) 0.95) but the same number was generated for every single row.

My query looks like something like this:

select
case when x=1 then (rand()*(0.85-0.95) 0.95) else 0
from abcd

CodePudding user response：

Maybe use NEWID for generating random numbers; as RAND() will give same values for all rows in the table as seed does not change. We can use NEWID to feed new seed values per row and get a diff random number.

so your case condition is more like

 (RAND(CHECKSUM(NEWID()))*(0.85-0.95) 0.95)

fiddle

and your query like

select
case when x=1 then (RAND(CHECKSUM(NEWID()))*(0.85-0.95) 0.95) else 0
from abcd

CodePudding user response：

RAND() returns the same number for a given statement. A solution would be to use NEWID(). Something like:

CONVERT( DECIMAL(2, 2), .85 (.95 - .85 )*RAND(CHECKSUM(NEWID())))

Fiddle here