 |
|
SQL Product Highlight
Devart - dbForge SQL Complete
dbForge SQL Complete is a code autocomplete tool for SQL Server Management Studio and Visual Studio. Free and advanced paid editions of this useful add-in offer powerful autocompletion and formatting of T-SQL code that replaces native Microsoft T-SQL Intellisense.Learn more!
Randomly Retrieve SQL Server Records
|
By: Greg Robidoux | Read Comments (1) | Print Greg is the President of Edgewood Solutions and a co-founder of MSSQLTips.com. Related Tips: More |
|
Problem
Have you ever had the need to randomly retrieve a set of records from a database table to display to users? One way of doing this is to use the RAND() function to generate a random number and then tie this back to your original record set. The RAND() function works as follows:
| SELECT RAND() |
| Returns result 0.7154366573674853 |
The problem with using the RAND() function is that it sets the same random value for the entire recordset. So if we run this query we get the following result with each record having the same RandomValue.
| SELECT TOP 5 ProductName, RAND() as RandomValue FROM products ORDER BY 2 | ||||||||||||
|
Another approach would be to to create a User Defined Function (UDF) to return a random number. The random number would then be calculated for each record in the record set. This approach would work, but seems a bit complex for what we are trying to achieve.
Solution
A simple solution would be to use the NEWID() function. The NEWID() function returns a uniqueidentifier for each row of data that is returned. So to simply retrieve the first 5 rows from the products table in the Northwind database we would issue the following statement.
| SELECT TOP 5 productName FROM products ORDER BY NEWID() |
The first run returns the following recordset
The second run returns a totally different recordset
|
So you can see achieving a totally random list of records is very straight forward using the NEWID() function.
Next Step
- To learn more about the NEWID() function click here
- If you need to retrieve a random list of records use this function in your SELECT statements
- Watch out on very large tables to make sure this does not become a performance overhead, one option is to use a WHERE clause to limit the scope of records that you are working with and therefore the result will be much faster.
Last Update: 6/29/2006
| Share: |  |
Share | Tweet |
|
 |
 |
Connect with MSSQLTips.com |
Comments and Feedback:
| Monday, November 17, 2008 - 6:23:48 AM - Rohit Madhok | Read The Tip |
|
When selecting rows randomly from a large table one may refer to http://msdn.microsoft.com/en-us/library/cc441928.aspx e.g. SELECT top 100 pk_column_id FROM MyTable WHERE (ABS(CAST((BINARY_CHECKSUM(pk_column_id, NEWID())) as bigint)) % 100) < 10 where, 'pk_column_id' is the name of the primary key column (defined as bigint) |
|
|
privacy | disclaimer | copyright | advertise | about authors | contribute | feedback | giveaways | user groups Some names and products listed are the registered trademarks of their respective owners. Edgewood Solutions LLC | MSSharePointTips.com | MSSQLTips.com |