Random Banner Ad with WHERE - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Random Banner Ad with WHERE

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2001-12-05 : 08:53:34

Jassim writes "How can I select a random banner ad based on the ID primary key WHERE the banner_url field is NOT Null?"

jackstow
Posting Yak Master

160 Posts

Posted - 2002-01-18 : 05:55:12

I've read the article on banner rotation and like it a lot. However, I think the question here is valid. Suppose I have a field in the banners table which checks if the banner is active or disabled. How do i randomly select a banner without getting a NULL result. For example;

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
AND active = 1
Order by WeightScaled ASC

- this works if one of the records you are looking for (i.e. those where active = 1) has the highest WeightScaled in the table. If not, however, a blank row will be returned every now and again.
The way to do it seems to be to check the active field on both of the select statements;

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

Select @RandomNumber = rand() * @MaxValue

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

..though I still have a problem getting it to work if there is a JOIN involved in the SELECT.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources