Ike, your query will still attempt to select records that have been deleted The original problem was that the keys werent sequential if some records have been deleted. That's going to happen as ads are removed. Using the technique I use, it selects a random record from the records that exist in the table, whether sequential or not.
IN fact if you had 4 ads on the page, you could randomly select 4 of them with this: <CFQUERY DATASOURCE="#Green#" NAME="GetADs"> SELECT top 4 * FROM dbo.banner_left <!---- if you want to add some restrictions like for example only select from the ones that havent been displayed recently ----- > WHERE lastdisplay < 'whatever' ORDER BY newID() </CFQUERY> Ike, your solution requires two trips to the database, mine requires one. So . my dad can beat your dad . nyah nyah nyah !! <g> Cheers Mike Kear Windsor, NSW, Australia Adobe Certified Advanced ColdFusion Developer AFP Webworks http://afpwebworks.com ColdFusion, PHP, ASP, ASP.NET hosting from AUD$15/month On Thu, Dec 4, 2008 at 3:38 PM, s. isaac dealey <[EMAIL PROTECTED]> wrote: > Here's my suggested modification > > <!--- Gets Maximum number of ads---> > <CFQUERY DATASOURCE="#Green#" NAME="getCachedAds" > cachedwithin="#CreateTimeSpan(1,0,0,0)#"> > SELECT * dbo.banner_left > </CFQUERY> > > <!--- RandRange is Random integer between first number and second(1st,2nd) > ---> > <CFSET ADID = getCachedAds.ADID[RandRange(1,getCachedAds.recordcount)]> > > <!--- Calls up all columns in table ---> > <CFQUERY NAME="GetADs" dbtype="query"> > SELECT * > FROM getCachedAds > WHERE ADID = #ADID# > ORDER BY ADID > </CFQUERY> > > Not sure why there even was an order by in that query originally, since > it will only return one record anyway. Probably won't affect performance, > but you may as well drop the order by. > > hth, > ike > > -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Adobe® ColdFusion® 8 software 8 is the most important and dramatic release to date Get the Free Trial http://ad.doubleclick.net/clk;207172674;29440083;f Archive: http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:316234 Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
