SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Create random number
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

GaryNull
Starting Member

USA
14 Posts

Posted - 12/27/2013 :  15:58:14  Show Profile  Reply with Quote
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






Edited by - GaryNull on 12/27/2013 16:59:55

cgraus
Starting Member

Australia
12 Posts

Posted - 12/27/2013 :  16:18:26  Show Profile  Reply with Quote
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()

Edited by - cgraus on 12/27/2013 16:26:14
Go to Top of Page

GaryNull
Starting Member

USA
14 Posts

Posted - 12/27/2013 :  16:25:32  Show Profile  Reply with Quote
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
........
Go to Top of Page

cgraus
Starting Member

Australia
12 Posts

Posted - 12/27/2013 :  16:26:55  Show Profile  Reply with Quote
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.
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17681 Posts

Posted - 12/27/2013 :  22:29:50  Show Profile  Reply with Quote
select	dbo.F_RANDOM_INTEGER(28, 57, newid()) / 100.0


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


KH
Time is always against us

Go to Top of Page

cgraus
Starting Member

Australia
12 Posts

Posted - 12/27/2013 :  22:57:37  Show Profile  Reply with Quote
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.
Go to Top of Page

GaryNull
Starting Member

USA
14 Posts

Posted - 12/27/2013 :  23:34:32  Show Profile  Reply with Quote
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
Go to Top of Page

cgraus
Starting Member

Australia
12 Posts

Posted - 12/28/2013 :  00:34:16  Show Profile  Reply with Quote
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.
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 12/28/2013 :  05:34:24  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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
Go to Top of Page

SwePeso
Patron Saint of Lost Yaks

Sweden
30281 Posts

Posted - 12/28/2013 :  05:38:21  Show Profile  Visit SwePeso's Homepage  Reply with Quote
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



Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA
Go to Top of Page

waterduck
Aged Yak Warrior

Malaysia
965 Posts

Posted - 12/29/2013 :  22:04:11  Show Profile  Reply with Quote
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)

Edited by - waterduck on 12/29/2013 22:16:50
Go to Top of Page

waterduck
Aged Yak Warrior

Malaysia
965 Posts

Posted - 12/29/2013 :  22:45:39  Show Profile  Reply with Quote

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)
Go to Top of Page

waterduck
Aged Yak Warrior

Malaysia
965 Posts

Posted - 12/29/2013 :  22:48:13  Show Profile  Reply with Quote
XD finally can win peso 1 query mua ha ha ha
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000