Your problem is in needing 3 records, not just one. So you can't really be selecting "random" records. By that, I mean, if you randomly select record "6," then you need to exclude it from the possible results of the next random number selection.
Two thoughts: either break the 100 records into subsets (1-33, 34-66, 67-100) and get a random number from each of those ranges. Or set up a loop that checks the results of earlier number generation and won't allow a duplicate.
The problem with the latter scenario is that you could theoretically run into a infinite loop. Not likely, but you could run into duplications with some frequency.
First, let's assume you truly have exactly 100 records, and either their primary keys are 1-100, or they have a 1-100 value assigned in another field.
For instance, you might have an entry that looks like:
PrimaryKeyID RecordNo (1-100) LinkInfo
You'd create the three numbers with the RandRange function. So you could do:
<cfset link1_RecordNo = RandRange(1,33) />
<cfset link2_RecordNo = RandRange(34.66) />
<cfset link2_RecordNo = RandRange(67,100) />
Then use those variables to pull the link info from the database.
The loop variation would be more like:
<cfset linkRecordNumberList = RandRange(1,100) />
<cfloop condition="ListLen(linkRec
<cfset nextLinkNumber = RandRange(1,100) />
<cfif NOT ListFind(linkRecordNumberL
<cfset ListAppend(linkRecordNumbe
</cfic>
</cfloop>
Then the linkRecordNumberList should eventually have 3 pseudo-random values, all different, between 1 and 100, which you could use to query your database. As I say, the likelihood that the RandRange function would keep returning the same number, which would be kicked out, is small. But there's a definite inefficiency there.
If nobody minds your making the selection a little less "random" by splitting them into 3 ranges, that'd be better.
Hope that helps,
Tom
Main Topics
Browse All Topics
by: mkishlinePosted on 2006-03-06 at 14:41:23ID: 16119475
I believe you can do something like this.
<cfquery name="queryname" datasource="dbname">
SELECT TOP 3
FROM tablename
ORDER BY NewID()
</cfquery>
I haven't used access for a long time, but another alternative would be:
<cfquery name="queryname" datasource="dbname">
SELECT *
FROM tablename
ORDER BY Rand()
LIMIT 3
</cfquery>