Browse DevX
Sign up for e-mail newsletters from DevX

Tip of the Day
Language: Active Server Pages (ASP)
Expertise: Beginner
May 26, 2000



Building the Right Environment to Support AI, Machine Learning and Deep Learning

Randomly Retrieve Records From a Database


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?


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 Table
    where 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.

DevX Pro
Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



Thanks for your registration, follow us on our social networks to keep up-to-date