title: Want an efficient approach to retrieving records from a database when the retrieval is weighted and balanced
body: <p>Im working on something incredibly unique..... a property listings website. ;)</p>
  
  <p>It displays a list of properties. For each property a teaser image and some caption data is displayed. If the teaser image and caption takes a site visitors interest, they can click on it and get a full property profile. All very standard.</p>
  
  <p>The customer wants to be able to allow property owners to add multiple teaser images and to be able to track which teaser images got the most click throughs. No worries there.</p>
  
  <p>But they also want to allow the property owner to weight each teaser image to control when it is shown. So for 3 images with weightings of 2, 6, 2, the 2nd image would be shown 6/10 times. This needs to be balanced. If the first 6 times the 2nd image is shown, it cant be shown again until the 1st and 3rd images have be shown twice each.</p>
  
  <p>So I need to both increment how often an image has been retrieved and also retrieve images in a balanced way. Forget about actual image handling, Im actually just talking about Urls.</p>
  
  <p><em>Note incrementing how often it has been retrieved is a different animal to incrementing how often it has captured a click through.</em></p>
  
  <p>So i can think of a few different ways to approach the problem using database triggers or maybe some LINQ2SQL, etc but it strikes me that someone out there will know of a solution that could be orders fo magnitude faster than what i might come up with.</p>
  
  <p>My first rough idea is to have a schema like so:</p>
  
  <p>TeaseImage(PropId, ImageId, ImageUrl, Weighting, RetrievedCount, PropTotalRetrievedCount)</p>
  
  <p>and then</p>
  
  <pre><code>select ImageRanks.*
  from (Select t.ImageID, 
               t.ImageUrl, 
               rank() over (partition by t.RetrievedCount order by sum(t.RetrievedCount) desc) as IMG_Rank 
         from TeaseImage t
       where t.RetrievedCount&lt;t.Weighting
      group by t.PropID) ImageRanks
  where ImageRanks.IMG_Rank &lt;= 1
  </code></pre>
  
  <p>And then </p>
  
  <pre><code> 1. for each ImageId in the result set increment RetrievedCount by 1 and then
   2. for each PropId in ResultSet increment PropTotalRetrievedCount by 1 and then 
   3. for each PropId in ResultSet check if PropTotalRetrievedCount ==10 and if so reset it to PropTotalRetrievedCount = 0 and RetrievedCount=0 for each associated ImageId
  </code></pre>
  
  <p>Which frankly sounds awful :(</p>
  
  <p>So any ideas?
  Note: if I have to step out of the datalayer I'd be using C# / .Net. Thanks.</p>