I learned something new today.  Using ROWNUM in an Oracle query, while it does limit the resultset to the number specified, does not limit it to the top n results.  It’s basically a random sample because it appears that ROWNUM is calculated before the ORDER BY clause.

I’ve got a set of data that’s thousands, upon thousands in size that will choke any browser if I try to send the complete set as a JSON string and try to render it, so I’m attempting to page the results.

What I ended up having to do is two-subselects in order to get the paging right.

<cffunction name="getByAttributes" access="public" output="false" returntype="query">
	<cfargument name="attr1" type="numeric" required="false" />
	<cfargument name="attr2" type="string" required="false" />
	<cfargument name="attr3" type="string" required="false" />
	<cfargument name="page" type="numeric" required="false" default="1" />

       <cfset var local = {} />
	<cfset local.rowEnd = arguments.page * 100 />
	<cfset local.rowStart = ((arguments.page - 1) * 100) + 1 />

       <cfquery name="local.pagedQuery" datasource="#variables.datasource.getName()#">
       SELECT
           y.attr1
           y.attr2,
           y.attr3,
       FROM (
           SELECT
               x.attr1,
               x.attr2,
               x.attr3
               rownum r
           FROM (
               SELECT
                   fi.attr1,
                   fi.attr2,
                   fi.attr3
               FROM funny_info fi
               ORDER BY fi.attr2
           ) x where rownum <= <cfqueryparam cfsqltype="cf_sql_integer" value="#local.rowEnd#" />
       ) y where r >= <cfqueryparam cfsqltype="cf_sql_integer" value="#local.rowStart#" />
       </cfquery>

	<cfreturn local.pagedQuery />
</cffunction>