How to return a random record
To return a random record from a resultset we append a random, unique, value to each record. We order the resultset by these random values, and use SQL's TOP clause to restrict the number of records coming back into our ADO recordset.

Microsoft Access
For Microsoft Access we can return a set of one or more random records by using the RND() function to order the resultset. The only difficulty is that the RND() seed is cached resulting in the same ordered set of results if we use RND() directly in the SQL statement.

To get around this we pass in a different value to the RND() function each time, forcing Access to use a new seed when generating the random number. Assume that we have a table called: Table1 and this contains fields: TableID and Field1

<%
' Initialize ASP RND() function
Randomize()
intRandomNumber = Int (1000*Rnd)+1

' Return 3 random records
strSQL = _
   "SELECT TOP 3 TableID, Field1, Rnd(" & -1 * (intRandomNumber) & "*TableID)" & _
   "FROM Table1 " & _
   "ORDER BY 3"

Set objRS = objConn.Execute(strSQL)
%>

Microsoft SQL Server
For Microsoft SQL Server we use the NewId() function to return values of data type unique indentifier. These are similar to Windows GUIDs in that the current date/time is used to seed the random value generator. Again we use the ordinal position of the "random value" column to order the resultset. We are using the default Northwind DB that comes with SQL Server 2000. The following T-SQL can be run in Query Analyser.

Bill Wilkinson of Microsoft has kindly pointed out that this is not going to be a light operation if you have many, many records (say, a few hundred thousand). However, if you have use a WHERE clause to restrict the number of records that you are creating GUIDs for, then SQL Server will apply that before creating the GUIDs. For instances where you wish to return just a single record (rather than, say 5 random records), it may be better to create a static table with sequentially numbered records, and use a random number generator to determine which record should be returned.

USE Northwind

SELECT
   TOP 1 CategoryID,
   CategoryName FROM
   Categories
ORDER BY
   NewID()

Back to FAQ Listing