> I have a database of 4,000 or so records. I would like to do a query based
> on some conditions (i.e. where active =1 AND group_id = 45). I
> can do this.
> The problem I have having is that out of the conditional query I
> would like
> to pull a random 3 records out and have use and be able to output the data
> in those random 3 rows. So, to recap.......4,000 records in a database. I
> might have a query where 500 out of the 4,000 match my
> conditional criteria.
> Out of that 500 that gets returned in the query I would like to select a
> random 3 out of only that 500 returned and have use of the data..
> Can anyone help?
OK, one simple solution to this would be to run your criteria, and only
return the Identity, then select 3 random entries from this (with a little
logic to ensure you don't have the same record twice), then get these 3
records from the main database;
<cfquery name="mySelection" datasource="myDSN">
select ID
from myTable
where myField='#myValue#' and myField2=#myValue2#
</cfquery>
<cfset myList="">
<cfloop index="i" from=1 to=3>
<cfset r=RandRange(1,mySelection.RecordCount)>
<cfloop condition="not ListFind(myList,mySelection.ID[r])">
<cfset r=RandRange(1,mySelection.RecordCount)>
</cfloop>
<cfset myList=ListAppend(myList,mySelection.ID[r])>
</cfloop>
<cfquery name="myItems" datasource="myDSN">
select *
from myTable
where ID in (#myIDs#)
</cfquery>
Problems will arise if your results are less than 3 records (infinite
loops), but you can add the logic for that.
HTH
Philip Arnold
ASP Multimedia Limited
T: +44 (0)20 8680 1133
"Websites for the real world"
**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.
**********************************************************************
------------------------------------------------------------------------------
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
