CFLib.org – Common Function Library Project

QueryRandomRows(theQuery, NumberOfRows)

Last updated July 10, 2002

author

Shawn Seley and John King

Version: 1 | Requires: CF5 | Library: DataManipulationLib

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:

<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:

/**
 * 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;
}
blog comments powered by Disqus

Search CFLib.org


Latest Additions

Kevin Cotton added
date2ExcelDate
May 5, 2016

Raymond Camden added
CapFirst
April 25, 2016

Chris Wigginton added
loremIpsum
January 18, 2016

Gary Stanton added
calculateArrival...
November 19, 2015

Sebastiaan Naafs - van Dijk added
getDaysInQuarter
November 13, 2015

Created by Raymond Camden / Design by Justin Johnson