remote
     synthesis

Handling Display Weight/Priority Using SQL

Posted on Jun 16, 2006

I was presented with an issue the other day while I was building our updated promo maintenance tool that handles internal banner ads. The issue was that the frequency of the display was weighted (I called it priority) like most banner ads are. In our case we had determined to limit the priority to 3 options, but the solution I will provide can work for more (but it has to be a limited set and likely small). Anyway, I was going to use the random records in T-SQL solution I previously discussed to send me back a single random record, therefore I needed to handle the priority within my query so that records with a higher priority were more likely to be randomly chosen. The solution I came up with feels a bit contrived, and I would love to hear a better one, but it works and is really easy to do. Nonetheless, I present this solution to both offer it to someone who may find it useful, but also to solicit feedback from someone who might have a better solution (kinda like Ray's Friday Puzzlers).In this case we had priorities of low (value 1), medium (2) and high (3). This solution would work if you choose 1 as your high priority value, but it becomes a little more complicated to add options later on (why will become clear). What I wanted was for the records with the higher priority to show up more frequently in my query and therefore have a higher chance of being randomly chosen. Rather than perform looping and iterate through the query to create copies or something along those lines, I decided to add a seperate table that just lists my priorities like so:

Notice that I have duplicated the priorities equivalent to their relative weight (i.e. 1 shows up once, 2 twice, 3 three times...and so on if you would like more options). Those of you who know SQL relatively well probably already guess what I do from here, I simply inner join the priority column in my promo table to the priority column in the table shown above. Thus, items with a priority 3 (high) will show up three times in my query. I then do a select top 1 and sort by newid() to randomize. In the end the query might look something like this (my actual query had alot more going on, but this is a very simplified version that deals only with this specific issue):

SELECT TOP 1 p.* FROM tblPromo p    INNER JOIN tblPromoPriority prp ON p.priority = prp.priority ORDER BY newid()

Like I said, the solution feels a bit contrived, but it appears to work well and performs quickly. If anyone knows of a better way to handle this type of situation, please share (as I do feel certain that their must be a better way, I am just not sure how).

Comments

Why not just do:

SELECT TOP 1 *
FROM tblPromo
ORDER BY priority DESC, newid()

Why do you need to link the tables since you want is the ad information? Also the way you wrote it, your not limiting the results by priority, so it will grab any priority.

What you really need to do is write a job that will update the promo table with fresher priorities say ever 14 minutes or so. This will make it so that you're not grabbing the same ads over and over.

Posted By Tony Petruzzi / Posted on 06/16/2006 at 11:11 AM