Bill Wilkinson's FAQ describes how to return a random record from a table. However, when you use a SQL Server database it's more efficient to use a stored procedure to return the records. For more information on using stored procedures read this article.
In this article Nathan Pond describes the use of a stored procedure to return a random record, but his method has some overhead because it creates a temporary table. This can be very ineffective when you have a large table.
My examples assume you have a table with an autoincrement ID field. It also covers the fact that there might be gaps in the ID field. (for example because some records were deleted) If your table does not have an autoincrement ID field you might want to use a second table which has an autoincrement ID field and holds a pointer to the actual table.
Now I’ll show you the code, and try to explain every step:
CREATE PROCEDURE spGetRandomRecord
AS
--Declare local variables DECLARE @counter int, @randno int, @uBound int, @lBound int
--You don’t want to get the results of the interim --SELECT statements passed to the recordset --so you have to set NOCOUNT on SET NOCOUNT ON
--Set the upper and lowerbound for the random number SELECT @uBound = Max(ID) FROM table SELECT @lBound = Min(ID) FROM table
--Get a random number SELECT @randno = Round(((@uBound - @lBound) * Rand() + @lBound), 0) SET @Counter = 0
--Keep trying until we find a record. This is necessary --if there are gaps in the ID field. WHILE @counter = 0 BEGIN --Check if there is an record with the specified ID IF EXISTS(SELECT ID FROM table WHERE id = @randno) BEGIN --The record exists, so get the complete record SET NOCOUNT OFF SELECT * FROM Table WHERE id = @randno --Set the @counter variable to 1 to leave the WHILE loop SET @counter = 1 END ELSE BEGIN --The SELECT didn't return any records, so get a --new random number and try again SELECT @randno = Round(((@uBound - @lBound -1 ) * Rand() + @lBound), 0) END END
The above solution is suitable when you have minor gaps in the ID field. If the gaps are fairly large it might take too long to find a record. To avoid this you could try to find a record in a range of id's and select one of them.
(Rest of the procedure remains the same)
--Check if there is an record in a range of id's --starting from the random number IF EXISTS(SELECT ID FROM table WHERE id BETWEEN @randno and @randno + 25) BEGIN --There were some records found in the specified --range, so select one of them SET NOCOUNT OFF SELECT TOP 1 * FROM Table WHERE id BETWEEN @randno and @randno + 25 --Set the @counter variable to 1 to leave the WHILE loop SET @counter = 1 END
Depending on the gaps in your id field you might want to change the 25 in something more appropriate for your situation, just test what works best in your situation.
Happy Programming!
FAQ posted by Dutch at
11/21/2001 7:46:30 AM to the
Databases, Queries category.
This FAQ has been viewed 66,022 times.
Do you have a FAQ you'd like to suggest?
Suggestions? Comments? If so, send it in!
Also, if you'd like to be a FAQ Admin (creating/editing FAQs),
let me know! If you are looking for other FAQs, be
sure to check out the 4Guys
FAQ and Commonly Asked Messageboard Questions!
