Question:

I would like to display a “Quote of the Day.” In order to do this, I need to retrieve the numbers of records in a recordset and then randomly choose from one of the retrieved records. How would I write this code?

Answer:

There are many different ways of doing this, depending on different scenarios. In your case, I would recommend that you have a unique ID field (a number) that identifies a single ‘Quote of the day’. It would be better if this number was a sequential number (like an AutoNumber or Identity field). Find out the range of numbers in your table and then pick a number at random. Finally return the record where the id matched the number.

To produce random integers in a given range, use this formula:

Int((upperbound - lowerbound + 1) * Rnd + lowerbound) 

Here, upperbound is the highest number in the range, and lowerbound is the lowest number in the range. Rnd is the function that returns a random number in VBScript/ASP. If you are using SQL Server, use the RAND() function.

If you are using SQL Server, you can create a stored procedure that does all the above in one call.:

Declare @LBound integer, @UBound integer Declare @OurID integer Select @LBound = min(IdentityField) from Table Select @UBound = max(IdentityField) from Table Select @OurID = CONVERT(integer, (RAND() *                (@Ubound - @Lbound+ 1) + @Lbound)) Select * from Table where IdentityField = @OurID 

Of course, you can make the above SQL more interesting by modifying it to obtain random values between ranges of numbers, or from records that meet a specific criteria etc.

Update: Thanks to reader Doug G. who pointed out some “issues” with the above SQL code. Here’s an updated version:

1. You do not need to make separate Select calls into a table to obtain the Min and the Max values
2. Try not to use a Select * if you do not need it; instead, enumerate the fields you need. Use this code instead:

   Declare @OurID integer Select @OurID = CONVERT(integer, (RAND() * (Max(IdentityField) - min(IdentityField)+ 1) + min(IdentityField))) from Table Select field1, field2, field3...from Tablewhere IdentityField = @OurID

3. Remember, you will get a null recordset if the ID you are looking for has been deleted. So, modify your code accordingly.