Archive for April, 2009
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




