Randomly Retrieve SQL Server Records


By:   |   Updated: 2006-06-29   |   Comments (1)   |   Related: More > Functions - System

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
ProductName RandomValue
Alice Mutton  0.80020779549998178
Aniseed Syrup  0.80020779549998178
Boston Crab Meat  0.80020779549998178
Camembert Pierrot  0.80020779549998178
Carnarvon Tigers  0.80020779549998178
 

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
  • Guaraná Fantástica
  • Tunnbröd
  • Ikura
  • Filo Mix
  • Genen Shouyu
     

The second run returns a totally different recordset

  • Mozzarella di Giovanni
  • Scottish Longbreads
  • Gustaf's Knäckebröd
  • Louisiana Fiery Hot Pepper Sauce
  • Filo Mix
     

So you can see achieving a totally random list of records is very straight forward using the NEWID() function.

 

Next Steps
  • 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 Updated: 2006-06-29


get scripts

next tip button



About the author
MSSQLTips author Greg Robidoux Greg Robidoux is the President of Edgewood Solutions and a co-founder of MSSQLTips.com.

View all my tips





Comments For This Article




Monday, November 17, 2008 - 6:23:48 AM - Rohit Madhok Back To Top (2215)

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)



download


Recommended Reading

Concatenate SQL Server Columns into a String with CONCAT()

SQL Server Rounding Functions - Round, Ceiling and Floor

SQL Server 2016 STRING_SPLIT Function

Using FOR XML PATH and STRING_AGG() to denormalize SQL Server data

Deciding between COALESCE and ISNULL in SQL Server





get free sql tips
agree to terms


Learn more about SQL Server tools