Create random number - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Create random number

Author

Topic

GaryNull
Starting Member

14 Posts

Posted - 2013-12-27 : 15:58:14

Is it possible to create random numbers
within a range like : .28 to .57 ?

UPDATE Nop_ProductVariant
SET percent = RAND() .28 to .57

So the percent field would look like :

ProdID percent
1001 .38
1002 .29
1003 .40
etc . .
all rows having a different value

cgraus
Starting Member

12 Posts

Posted - 2013-12-27 : 16:18:26

If you want a list of values in random order, with no repeats, this will do that:

WITH E1(N) AS
(
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS
(
SELECT 0 UNION ALL
SELECT TOP (1000)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM E4
)

select n/100.0 from cteTally where n >= 280 and n <= 570 order by newid()

You can simplify it by selecting top 570 and only doing a >= check, or even this:

WITH E1(N) AS
(
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL
SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1 UNION ALL SELECT 1
), --10E+1 or 10 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --10E+2 or 100 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --10E+4 or 10,000 rows max
cteTally(N) AS
(
SELECT 0 UNION ALL
SELECT TOP (500)
ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
FROM E4
)

select (n+270)/100.0 from cteTally order by newid()

GaryNull
Starting Member

14 Posts

Posted - 2013-12-27 : 16:25:32

thanks cgraus,
But is it possible to have a different random value for each row?
All the rows in the table contain the same value when I run that.
Is it possible to do this :

ProdID percent
1001 .35
1002 .27
........

cgraus
Starting Member

12 Posts

Posted - 2013-12-27 : 16:26:55

quote:
Originally posted by GaryNull

thanks cgraus,
But is it possible to have a different random value for each row?
All the rows in the table contain the same value when I run that.
Is it possible to do this :

ProdID percent
1001 .35
1002 .27
........

What I did was completely wrong :-) I went back and changed it.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2013-12-27 : 22:29:50

[code]select dbo.F_RANDOM_INTEGER(28, 57, newid()) / 100.0[/code]

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=69499

KH
[spoiler]Time is always against us[/spoiler]

cgraus
Starting Member

12 Posts

Posted - 2013-12-27 : 22:57:37

To be clear, the code you posted does not work without installing the functions behind it. Then, it might be a better solution than mine, if you want an endless stream of random values and don't care about duplicates.

GaryNull
Starting Member

14 Posts

Posted - 2013-12-27 : 23:34:32

I found this and it looks like it works.
This creates random number between 27 and 50 and a different value for every row :

SELECT table_name, 27.0 + floor(23 * RAND(convert(varbinary, newid())))as magic_number
FROM information_schema.tables
order by magic_number

cgraus
Starting Member

12 Posts

Posted - 2013-12-28 : 00:34:16

I ran this and the magic number sequence was all between 27 and 50. The sequence was 28, 29, 29, 33, 33, 33, 34, 35.... It will NOT give you a different value for each row, nor is it converting the values to the floating point range you wanted. The only way to get all unique values ( which you seem now to want ), is the way I did it, where each value is accounted for once, and the order is what's random. If you generate each number randomly, there will be repeats.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-12-28 : 05:34:24

quote:
Originally posted by GaryNull

Is it possible to create random numbers
within a range like : .28 to .57 ?

Yes, but there are only 30 unique numbers within your wanted interval.
Do you have more than 30 rows?

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-12-28 : 05:38:21

[code]UPDATE w
SET w.[Percent] = d.[Percent]
FROM (
SELECT TOP(30) [Percent],
ROW_NUMBER() OVER (ORDER BY NEWID()) AS rn
FROM dbo.Nop_ProductVariant
) AS w
INNER JOIN (
SELECT Number / 100E AS [Percent],
ROW_NUMBER() OVER (ORDER BY NEWID()) AS rn
FROM master.dbo.spt_values
WHERE Type = 'P'
AND Number BETWEEN 28 AND 57
) AS d ON d.rn = w.rn[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-12-29 : 22:04:11

Random number between 0.28 -> 0.57

SELECT 0.28 + ((ROUND(((CONVERT(DECIMAL(3,2), RAND()) % 1) * 100) / 3, 0)) / 100) - (ROUND(((CONVERT(DECIMAL(3,2), RAND()) % 1) * 100) / 25, 0) / 100)

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-12-29 : 22:45:39

[code]
UPDATE Nop_ProductVariant
SET [percent] = 0.28
+ (SELECT ((ROUND((RandumNumber.RandumNumber * 100) / 3, 0)) / 100) -(ROUND((RandumNumber.RandumNumber * 100) / 25, 0) / 100)
FROM (SELECT (ABS(CHECKSUM(NEWID())) % 100) / 100.0 AS RandumNumber)RandumNumber)
[/code]

waterduck
Aged Yak Warrior

982 Posts

Posted - 2013-12-29 : 22:48:13

XD finally can win peso 1 query mua ha ha ha

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources