Seeding SQL RAND() Method With NEWID() For Per-Row Random Values (Thanks Joshua Cyr)
Posted January 27, 2009 at 2:54 PM
From what I have read and from what I've seen demos of, I thought that MS SQL's RAND() method could only return one random value per connection. As I never looked at the documentation for RAND(), I never realized that it could be seeded. This morning, in my post on selecting random records from a weighted data table, Joshua Cyr pointed me to a great blog post demonstrating how to get RAND() to produce random numbers for each record in a query.
Before we see that, however, let's take a look at how it won't work:
Launch code in new window » Download code as text file »
- <!--- Select random numbers. --->
- <cfquery name="qRandom" datasource="#REQUEST.DSN#">
- SELECT
- p.id,
- ( RAND() ) AS random_number
- FROM
- pivot1000 p
- WHERE
- p.id < 10
- ORDER BY
- p.id ASC
- </cfquery>
-
-
- <!--- Output the random numbers generated. --->
- <cfloop query="qRandom">
-
- #qRandom.random_number#<br />
-
- </cfloop>
Notice here that we are simply selecting RAND() as a column in the table, then output that column. When we run this code, we get the following:
0.625088599784
0.625088599784
0.625088599784
0.625088599784
0.625088599784
0.625088599784
0.625088599784
0.625088599784
0.625088599784
Each call to RAND() produces the same result. This is because the RAND() method is implicitly seeded on the first call and then each subsequent call in the same connection uses the same seed value. To get a new random value for each RAND() method call, we have to pass it a new seed value each time.
The seed value must be numeric. Now, in the past, I've used NEWID() to create random numbers; but again, I never tried this on a per-row basis. As demonstrated in Josh's link, the NEWID() can be used to seed the RAND() method call every time it is run:
Launch code in new window » Download code as text file »
- <!--- Select random numbers. --->
- <cfquery name="qRandom" datasource="#REQUEST.DSN#">
- SELECT
- p.id,
-
- <!--- Seed RAND() with binary version of NEWID(). --->
- (
- RAND(
- CAST( NEWID() AS varbinary )
- )
- ) AS random_number
- FROM
- pivot1000 p
- WHERE
- p.id < 10
- ORDER BY
- p.id ASC
- </cfquery>
-
-
- <!--- Output the random numbers generated. --->
- <cfloop query="qRandom">
-
- #qRandom.random_number#<br />
-
- </cfloop>
As I have stated before, I still don't know what the conversion from UUID to VARBINARY does exactly, but it produces are binary data structure that then get implicitly cast to a numeric value when passed to the RAND() method. As a result, this seeds the RAND() method with a new value on each row and gives us the following output:
0.632351429673
0.277469709628
0.291371676631
0.187895386945
0.0332443201662
0.269721718406
0.801980914521
0.984558916944
0.569620907931
A random value for each record. Good stuff! Thanks a lot Josh!
Download Code Snippet ZIP File
Post Comment | Ask Ben | Permalink | Other Searches | Print Page
Newer Post
Ask Ben: Iterating Over An Array In jQuery, One Index Per Click
Older Post
Ask Ben: Selecting A Random Row From A Weighted, Filtered Record Set
Reader Comments
just in case you don't already know you can also use NEWID() to return random records from a table by using it in the ORDER BY clause:
ORDER BY NEWID()
you are they man. Great idea of of unique row values selection.
Thanks - this post helped me out :)
