I'm sure that Ben's solution will work but I've heard that RAND() in a where statement can be very slow (especially when run against large tables). I had to do something similar to pull a random item out of a table containing 900,000 records and RAND() pretty much choked the database so I came up with an alternative method.
In your case, I'd pull the IDs for the past two weeks of ads, get the recordcount from that query, set up a variable to hold a list of 20 record IDs then do something like this to pull a random ID out of the query's recordset: <cfset randomPick = RandRange(1, get2WkAds.recordcount, "SHA1PRNG")>. Add the record to your list (once confirming it's not already there) and continue until you have 20 items. Once you have 20, use the list to populate an ad retrieval query where the list is used in an IN statement in the WHERE clause. It's certainly more mechanical and requires more code than Ben's solution but it's just two queries (one quick and the other limited to pulling back just 20 specific records) and the looping/random ID generator should be pretty quick. On Mar 18, 12:17 pm, "ben.8" <[email protected]> wrote: > Are all 10k+ records 2 weeks old? What SQL engine? > > For MySQL Could do something like: > > SELECT column FROM table > WHERE dteCreated <= #dateadd("ww", -2, now())# > ORDER BY RAND() > LIMIT 20 -- official tag/function reference: http://openbd.org/manual/ mailing list - http://groups.google.com/group/openbd?hl=en
