– Common Function Library Project

QueryRandomRows(theQuery, NumberOfRows)

Last updated July 10, 2002


James Moberg

Version: 2 | Requires: CF10 | Library: DataManipulationLib

Returns a query object with a specified number of random records from the passed query. Some code based on QuerySlice() by Kevin Bridges (

Return Values:
Returns a query.


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

 * 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 James Moberg (
 * @version 1, July 10, 2002 
 * @version 2, January 16, 2018 by James Moberg (
   Update to CFFunction to avoid older Q-of-Q bugs. Dupe query, add temporary & random column value and requery w/max rows 
<CFFUNCTION NAME="QueryRandomRows" returntype="query" output="false" hint="Returns a randomized query">
    <CFARGUMENT NAME="theQuery" TYPE="query" REQUIRED="yes" HINT="database query">
    <CFARGUMENT NAME="NumberOfRows" TYPE="numeric" DEFAULT="5" REQUIRED="yes" HINT="maximum number of records to return">
    <CFSET var Temp = {
        FinalQuery = Duplicate(Arguments.theQuery),
        RandomColName = "Random#GetTickCount()#",
        thisRow = 0,
        MaxRows = VAL(Arguments.NumberOfRows),
        RandomPositions = []
    <CFIF Temp.FinalQuery.RecordCount LT Temp.MaxRows>
        <CFSET Temp.MaxRows = Temp.FinalQuery.RecordCount>
    <CFIF Temp.FinalQuery.RecordCount GT 1>
        <!--- Pick Random Position; generate array w/indexes, shuffle, the pick first X array items --->
        <CFLOOP FROM="1" TO="#Temp.FinalQuery.RecordCount#" INDEX="Temp.thisRow">
            <CFSET ArrayAppend(Temp.RandomPositions, JavaCast("int", Temp.thisRow))>
        <CFSET CreateObject("java", "java.util.Collections").Shuffle(Temp.RandomPositions)>
        <CFSET Temp.RandomPositions = Temp.RandomPositions.subList(0, Temp.MaxRows)>
        <!--- Add RandomX column to selected query rows, insert random values, requery & remove randomX column --->
        <CFSET QueryAddColumn(Temp.FinalQuery, Temp.RandomColName, "INTEGER", ArrayNew(1))>
        <CFLOOP ARRAY="#Temp.RandomPositions#" INDEX="Temp.thisRow">
            <CFSET Temp.FinalQuery[Temp.RandomColName][Temp.ThisRow] = javacast("int", RandRange(1, 2147483647, "SHA1PRNG"))>
        <CFQUERY NAME="Temp.FinalQuery" DBTYPE="query" MAXROWS="#Temp.MaxRows#">SELECT #Arguments.theQuery.ColumnList#
        FROM Temp.FinalQuery
        ORDER BY #Temp.RandomColName# DESC</CFQUERY>
    <CFReturn Temp.FinalQuery>


Latest Additions

Raymond Camden added
November 04, 2017

Leigh added
May 11, 2016

Raymond Camden added
May 10, 2016

Kevin Cotton added
May 05, 2016

Raymond Camden added
April 25, 2016

Created by Raymond Camden / Design by Justin Johnson