Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Get your favorite SSC scripts directly in SSMS with the free SQL Scripts addin. Search for scripts directly from SSMS, and instantly access any saved scripts in your SSC briefcase from the favorites tab.
Download now (direct download link)

Randomise the order of a SELECT

By EdH334,

I have a set of data that I need to display in a random order each time I displayed it. There are several ways to do this, for example using a CURSOR to add each record to a temp table with a GUID, but a quick and dirty way to do this is to use the RAND function.

When you put RAND into a SELECT it returns the same value for each row - not very useful in this case! To produce a random set of data I added a seed based on an IDENTITY column with an additional seed value (values arround the 10,000 mark work best). The random value is Modulo to 1000, because the higher parts of the RAND value aren't that random. The data part of the RAND is based on the code in the RAND entry in BOL. If someone has a better understanding of the RAND funciton they may be able to simplify this statement.

I haven't compared performance with the CURSOR method, but on small tables it seems works well. You can also check the results by averaging the random value, which should return roughly 500.

Total article views: 480 | Views in the last 30 days: 2
 
Related Articles
FORUM

Random number generator

Random numbers

FORUM

Random rows

Need random rows

FORUM

random records

select random records

FORUM

Random vs. sequential reads

Random vs. sequential reads

FORUM

Insert random values

Insert random values

Tags
miscellaneous    
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones