How does the seed value of RAND work?
-
I tried RAND(AValue) with some values, but they give almost the same result value.
EG
SELECT rand(3)*100, rand(10)*100, rand(24)*100, rand(DATEPART(ms, GETDATE()))*100, DATEPART(ms, GETDATE())
result: 71.36.., 71.37.., 71.40.., 71.51.., 623
Does not look random to mee
But when using huge numbers
SELECT rand(3*1000000)*100, rand(10*1000000)*100,rand(24*1000000)*100, rand(DATEPART(ms, GETDATE())*1000000)*100
result: 61.24.., 4.32.., 90.48.., 5.48..
It looks more random
Can anyone tell me, what seed values I have to use for getting random numbers (I don't need exact randomness)
I want these random figures for ordering items, so that I can find two random items to show on the home-page.
I can't use RAND(), without a seed, because it gives me the same value in each record. And I also can't use ORDER BY NewID() because I have a weight factor (between 1 and 10) to expand the chance that some items are more shown on the home page
-
This is what I found on Google:
What if someone told you that RAND(N) was the following simple function:
RAND(N) = the fractional part of 0.713573+N*1.8633E-05
Well guess what. That's what RAND(N) is (I'm only correct to about 5 places).
RAND() is something much better.
Now do you think all the great mathematics behind pseudo random
number generators should be thrown out and replaced by using this
very boring, and very slowly changing function?
-------------
And a solution I found on Google:
select RAND( cast( newid() as varbinary(128) ) )
-
It doesn't work quite like you'd expect... although the VALUE of the seed works as posted above and in Books-Online, it doesn't work WHEN you'd like it to if you use RAND() after the initial seeding...
SELECT RAND(25)
SELECT RAND()
FROM Northwind.dbo.Employees
The first RAND returns some random number and it will be different than the second.
The secont RAND returns some random number different than the first but it returns the same random number for each row in the table. Not good for many things some of us want to do with random numbers...
So, you actually have to use a different random number to provide a random seed for RAND...
SELECT RAND(CAST(NEWID() AS VARBINARY))
FROM Northwind.dbo.Employees
The above will return a different random number for each row in the table.
Addendum: Didn't read Henk's post all the way... he came up with the same answer before I did
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Jeff,
Still thanks for your solution. Fine we found the same.
-
Roger that! Confirmation in both directions is always a wonderful thing...
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply