CFLib.org – Common Function Library Project

QueryRandomRows(theQuery, NumberOfRows)

Last updated July 10, 2002

Version: 1 | Requires: ColdFusion 5 | Library: DataManipulationLib

 
Rated 4 time(s). Average Rating: 4.8

Description:
Returns a query object with a specified number of random records from the passed query. Some code based on QuerySlice() by Kevin Bridges (cyberswat@orlandoartistry.com) Please note that results below will not be random due to caching at cflib.org.

Return Values:
Returns a query.

Example:

view plain print about
<CFSET Foo = QueryNew("name,age,rank")>
<CFLOOP INDEX="X" FROM=1 TO=10>
<CFSET QueryAddRow(Foo)>
<CFSET QuerySetCell(Foo,"name","Random Name #X#")>
<CFSET QuerySetCell(Foo,"age",RandRange(20,50))>
<CFSET QuerySetCell(Foo,"rank",RandRange(1,10))>
</CFLOOP>
<CFOUTPUT>Original...</CFOUTPUT>
<CFDUMP VAR="#Foo#">
<CFSET Shorter = QueryRandomRows(Duplicate(Foo), 3)>
<br>
<CFOUTPUT>3 Random Rows...</CFOUTPUT>
<CFDUMP VAR="#Shorter#">

Parameters:

Name Description Required
theQuery The query to return random records from. Yes
NumberOfRows The number of random records to return. Yes

Full UDF Source:

view plain print about
<cfscript>
/**
 * Returns specified number of random records.
 * 
 * @param theQuery      The query to return random records from. (Required)
 * @param NumberOfRows      The number of random records to return. (Required)
 * @return Returns a query. 
 * @author Shawn Seley and John King (shawnse@aol.com) 
 * @version 1, July 10, 2002 
 */

function QueryRandomRows(theQuery, NumberOfRows) {
    var FinalQuery      = QueryNew(theQuery.ColumnList);
    var x                = 0;
    var y               = 0;
    var i               = 0;
    var random_element  = 0;
    var random_row      = 0;
    var row_list        = "";

    if(NumberOfRows GT theQuery.recordcount) NumberOfRows = theQuery.recordcount;

    QueryAddRow(FinalQuery, NumberOfRows);

    // build a list of rows from which we will "scratch off" the randomly selected values in order to avoid repeats
    for (i=1; i LTE theQuery.RecordCount; i=i+1) row_list = row_list & i & ",";

    // Build the new query
    for(x=1; x LTE NumberOfRows; x=x+1){
        // pick a random_row from row_list and delete that element from row_list (to prevent duplicates)
        random_element  = RandRange(1, ListLen(row_list));          // pick a random list element
        random_row      = ListGetAt(row_list, random_element);      // get the corresponding query row number
        row_list        = ListDeleteAt(row_list, random_element);   // delete the used element from the list
        for(y=1; y LTE ListLen(theQuery.ColumnList); y=y+1) {
            QuerySetCell(FinalQuery, ListGetAt(theQuery.ColumnList, y), theQuery[ListGetAt(theQuery.ColumnList, y)][random_row],x);
        }
    }

    return FinalQuery;
}
</cfscript>
blog comments powered by Disqus

Search CFLib.org


Latest Additions

Adam Cameron Adam Cameron added
createPrimeNumbe...
14 day(s) ago

Ray Ford Ray Ford added
timeZoneNow
29 day(s) ago

Henry Ho Henry Ho added
queryExecute
a while ago

Rick Root Rick Root added
deleteDirectory
a while ago

Top Rated

Darwan Leonardo Sitepu backupDatabase
Rated 5.0, 48 time(s)

Barney Boisvert indentXml
Rated 5.0, 12 time(s)

Rachel Lehman deAccent
Rated 5.0, 9 time(s)

Darwan Leonardo Sitepu splitNumber
Rated 5.0, 8 time(s)

Created by Raymond Camden / Design by Justin Johnson