– 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

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

Return Values:
Returns a query.


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


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
 * 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 ( 
 * @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


Latest Additions

CF Ninja CF Ninja added
9 day(s) ago

Stephen Withington Stephen Withington added
9 day(s) ago

Adam Cameron Adam Cameron added
a while ago

Ray Ford Ray Ford added
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