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.
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
intRandomNumber = Int (1000*Rnd)+1
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
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.
TOP 1 CategoryID,
Back to FAQ Listing