Login | Register   
LinkedIn
Google+
Twitter
RSS Feed
Download our iPhone app
TODAY'S HEADLINES  |   ARTICLE ARCHIVE  |   FORUMS  |   TIP BANK
Browse DevX
Sign up for e-mail newsletters from DevX


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

Randomly Retrieve Records From a Database

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

 

 

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