0

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
Henfu
  • 11
  • 3
  • Does this answer your question? [How do I generate a random number for each row in a T-SQL select?](https://stackoverflow.com/questions/1045138/how-do-i-generate-a-random-number-for-each-row-in-a-t-sql-select) – Thom A Apr 22 '22 at 16:58
  • https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=6ceb09e733b44820c7c45e7180aa9d67 – Aaron Bertrand Apr 22 '22 at 17:04

2 Answers2

1

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
DhruvJoshi
  • 17,041
  • 6
  • 41
  • 60
1

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

Edward Radcliffe
  • 537
  • 2
  • 11