Hey Chris, Try this:
SELECT * FROM (SELECT * from ads ORDER by rand()) as randomized_ads /* So the GROUP BY is random, else it will always pick the first ad in a group */ GROUP BY COALESCE(exclusion_group_id, rand()) /* If no exclusion group, assign random number */ ORDER BY rand() /* Randomize again */ limit 2 /* Only show 2 ads */ Performance is really dictated by your table and needs. Subqueries aren't great, but depending on the table setup it could run fine. This could be fine tuned by limiting the sub query and some other things. Jeff On Tue, Dec 28, 2010 at 12:12 AM, Chris McCann <[email protected]>wrote: > Before I go off and invent something on my own I figured I'd ask here > if anyone has done something similar. > > We have a "sponsored links" section in our app that effectively > contains paid advertising. We're finding that branch offices of the > same company want to run ads but they want some assurance that their > Company X - San Diego ad won't be on the same page as Company X - Bay > Area since they tend to use the same corporate logo. > > I was just picking a random set of ads and displaying them but now I > need to ad more logic to apply the mutual exclusion within one > company's offices, or as I'm calling it, apply exclusion groups. > > The key requirements are 1) be fast (since we show 3 randomly-selected > ads on pretty much every page of the app) and 2) honor the exclusion > group requirement so that only one ad from an exclusion group will be > displayed in a batch. > > Has anybody here done something like this before in a Rails app and > has code or approaches they can share? > > Cheers, > > Chris > > -- > SD Ruby mailing list > [email protected] > http://groups.google.com/group/sdruby > -- Jeff Berg Owner/Developer Ministry Centered Technologies ================================= REVOLUTIONIZE the way you plan your SERVICES at planningcenteronline.com REVOLUTIONIZE the way you plan your EVENTS at smartevents.com -- SD Ruby mailing list [email protected] http://groups.google.com/group/sdruby
