?
Solved

RAND returns same dataset everytime it's run

Posted on 2011-05-03
5
Medium Priority
?
267 Views
Last Modified: 2012-05-11
I want to extract a random list of X number of customers every month. Here is my query:

SELECT TOP (10) Customers.Name, Customers.Address, Customers.City, Customers.State, Customers.ZipCode, RAND() AS Random
FROM Customers

Microsoft says to leave the seed blank to get a random dataset each time, but this query is getting the same dataset everytime.
0
Comment
Question by:LezlyPrime
  • 3
5 Comments
 
LVL 32

Accepted Solution

by:
Ephraim Wangoya earned 252 total points
ID: 35518115

Use this instead
SELECT TOP (10) Customers.Name, Customers.Address, Customers.City, Customers.State, Customers.ZipCode
FROM Customers
order by NEWID()

Open in new window

0
 
LVL 29

Assisted Solution

by:Paul Jackson
Paul Jackson earned 248 total points
ID: 35518117
SELECT TOP (10) newid() as row, Customers.Name, Customers.Address, Customers.City, Customers.State, Customers.ZipCode
FROM Customers
Order By row

http://www.techrepublic.com/article/generate-random-sets-in-sql-server-with-newid/6089823
0
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 35518134
You want to test what further ? It is also no reason to close the question after perfectly valid solutions have been given.
0
 
LVL 29

Expert Comment

by:Paul Jackson
ID: 35518138
Microsofts own documentation says that successive calls to rand() with the same seed produces the same results.

http://msdn.microsoft.com/en-us/library/ms177610.aspx
0
 

Author Closing Comment

by:LezlyPrime
ID: 35690001
Before logging off last night I ran the query without any of the RAND stuff & got the same results & thought, "I'm way off base here." I was right.

Great answers & both worked. Thanks.
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

862 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question