I’m trying to do a list of results from a query with upper and lower bounds. Normally one would just do a query then add the StartRow and MaxRows attributes to a cfloop or cfoutput, but I’m stubborn / don’t want to do it that way. QoQ allows for a max row argument, but not a start row.

My cfm page calls a cfc which has a function for a standard query, which I’m caching. I have another function that is going to list results by paging and by search params. I don’t want to touch the cached query so here’s what I’m going to do.

<cfquery datasource="mydsn"
		cachedwithin="#CreateTimeSpan(0,1,0,0)#"
		name="qryProps">
	SELECT *, @intRow:=@intRow+ 1
	FROM myTable, (SELECT @intRow:=0) AS r
	ORDER BY p_id
</cfquery>

<cfquery dbtype="query" name="QoQProps">
	SELECT ∗
	FROM qryProps
	WHERE intRow
		BETWEEN #offStart# AND #offLimit + 1#
</cfquery>

As long as the order of the first query is expected (I have it under control) then it’s going to work awesome.

For those curious, here are methods for getting a row number into the query in…

MSSQL:
SELECT ∗, ROW_NUMBER() OVER(ORDER BY p_id) AS intRow
FROM myTable

ORACLE:
SELECT ∗, ROWNUM as intRow
FROM myTable