By: Greg Robidoux | Last Updated: 2006-06-29 | Comments (1) | Functions - System
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:
|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.
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.
- 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
About the author
View all my tips