How to generate random numbers for multiple rows in a column?

Question

I need to generate random values for multiple rows in a column. The random values need to be in the range of 0.005 and 0.015 inclusive.

I have tried using the abs(checksum(newID()) but I am not sure how to specify a range using that.

Gordon Linoff · Accepted Answer · 2019-07-12 22:13:29Z

1

You can use rand() and arithmetic:

select rand(checksum(newID())) * 0.01 + 0.005

edited Jul 12, 2019 at 22:13

answered Jul 12, 2019 at 21:55

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

sanjay sisodiya · Accepted Answer · 2019-07-16 06:02:33Z

0

You can give a range like this by using the RAND() function.

Please execute SQL and you can see. The number that comes will be within the range.

SQL:

SELECT RAND()*(0.015-0.005)+0.005 as randomNumber

Here, 0.015 and 0.005 are range number.

SQL output

|----|-------|----------------------|
|id  |empCode| randomNumber         |
|----|------------------------------|
|1   | 0001  | 0.01245219297469798  |
|----|-------|----------------------|
|2   | 0002  | 0.010188965811570142 |
|----|-------|----------------------|

Hopefully, your problem will be resolved.

edited Jul 16, 2019 at 6:02

answered Jul 13, 2019 at 12:11

sanjay sisodiya

4654 silver badges16 bronze badges

3 Comments

Franktank24 Over a year ago

This will generate the same random number for every row. Not what I am looking for.

sanjay sisodiya Over a year ago

@Franktank24 I have executed SQL Query and a unique number is generated for each line from the query.

sanjay sisodiya Over a year ago

@Franktank24 I have added the output of the SQL query in the above answer, please check.

user10223479 · Accepted Answer · 2023-07-12 09:43:20Z

0

Late to the game... but here's a good solution: Random number in range.

ABS(CHECKSUM(NEWID()))%<Number of Values in Range> + <Lowest Value in Range> + 1

So here we get a RandomNumber for each row with values between 10 and 20

SELECT FirstName,MiddleName,LastName, ABS(CHECKSUM(NEWID()))%10 + 11 AS RandomNumber
FROM Person.Person

Taken from https://sqlundercover.com/2017/06/22/generating-random-numbers-for-each-row-over-a-specified-range-and-other-funky-stuff-you-can-do-with-them/

answered Jul 12, 2023 at 9:43

user10223479

256 bronze badges

Collectives™ on Stack Overflow

How to generate random numbers for multiple rows in a column?

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related