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 <[email protected]> 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" <[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
>
--
official tag/function reference: http://openbd.org/manual/
mailing list - http://groups.google.com/group/openbd?hl=en
