Re: [OpenBD] Re: Best way to randomize results from cfsearch collection

[Alan Holden]

You're getting closer, but your method does not guarantee that a record will actually exist in the table which bears a PK number matching one in your random integers list. Records can get deleted, no? Run a query for the past 2 weeks, but select JUST the ID numbers. Use the valueList() function to build a list from that, then loop and use the rndRange(1,listLen()) functions to build your target list of 20 winners. Do the full query (WHERE IN) from that. Al On 3/21/2011 2:44 PM, Jason King wrote: I'm using MS-SQL, and there's no 'limit' function. So at this point I'm using CFQUERY's maxrows to limit the results. However, I believe that the query is still grabbing more than 20 rows (being that I want 20 random ads), it's just that it's being limited to 20 AFTER the query has run.  Rather than building a separate table to build a list for randomizing, couldn't I just query the table to get the rowcount, then just pull 20 random numbers from that rowcount? Let's say the rowcount is 15,000. I could pull 20 random numbers from 1 to 15,000.  Then, populate those numbers into an array. I'd have an array with 20 values.  ex.    1. SELECT COUNT(*) FROM tbl_new_ads 2. create array using 1 and count value as range 3. SELECT * FROM tbl_new_ads     Where rownumber = (array values) Does this seem logical? I know my syntax isn't correct but programatically it seems like a decent approach On Mon, Mar 21, 2011 at 10:23 AM, Craig328 <craig...@gmail.com> wrote: 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" <bkw0...@gmail.com> 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 -- official tag/function reference: http://openbd.org/manual/ mailing list - http://groups.google.com/group/openbd?hl=en -- official tag/function reference: http://openbd.org/manual/ mailing list - http://groups.google.com/group/openbd?hl=en