Returning a Single Random Row

By Bill Graziano on 27 September 2000 | Tags: Randomness

Earlier we published an article on quickly creating a sequential number. In this article we'll use that technique to quickly select one random record from a table. We'll even cover assigning a weight to each record. Can someone say banner ads?

This all started when I was trying to get banner ads up to cover the hosting costs for the site. I started using a company called BurstMedia. I've been happy with them overall but I also wanted the ability to run my own ads. I looked at downloading some free banner rotation software but I didn't find anything I was excited about. Plus, none of software used the cool new way to select a random record that I'm going to cover in this artcle. Make sure you read the article on Creating a Sequential Record number before you read this article.

We'll start with the table I use to hold my ads. The key fields look something like this:

BANNER_Ads
--------------
AdId int
AdName char(30)
Weight smallint
WeightScaled smallint

The primary key is AdId. I have more information in the table to actually figure out what ad to display. This solution works best if your table is relatively static. I also put an index on the WeightScaled field. We're going to write a stored procedure to that will update this entire table each time a record is modified. After any updates or inserts into the table you need to run the following code:

declare @Counter smallint
set @Counter = 0

update BANNER_Ads
SET @Counter = WeightScaled = @Counter + Weight

Update BANNER_Ads
SET WeightScaled = NULL
Where Weight <= 0

The second UDPDATE statement clears the WeightScaled field if the ad is not assigned a weight. This will prevent it from being selected. You could probably make that part of the first UPDATE statement if you really wanted to. The first UPDATE statement assigns the scaled weight.

In our example, I'll assume there are three ads. This is what the table will look like after the update has run.

AdId	Weight	WeightScaled
1	2	2
2	3	5
3	6	11

In my procedure to select a record I'll choose a random floating point number between 0 and 11. Values from 0 to 2 will choose the first record. Values greater than 2 through 5 will choose the second row and values greater than 5 through 11 will choose the last row. As you can see each row's Weight affects how often it is chosen.

My code to select a record looks like this:

Declare @MaxValue int, @RandomNumber float
Select @MaxValue = max(WeightScaled) from BANNER_Ads

Select @RandomNumber = rand() * @MaxValue

Select TOP 1 *
From BANNER_Ads
Where WeightScaled >= @RandomNumber
Order by WeightScaled ASC

I use the SQL Server function rand() to generate a random number. You can pass it a seed or have SQL Server determine a seed for you. It will use the same seed for each random number requested inside a batch. Since I only request one random number per batch I was ok using this approach.

I have this code inside a stored procedure. Every time I call it I get one random row back from the table. You could also do impression logging from inside this procedure but that's a topic for another day. Enjoy! fyi - you can view another article based on this with the ASP code at 4GuysFromRolla.com