If you are looping over the query with a cfoutput tag, you can specify the
startRow and maxRows attribute to make pagination easy. That actually
modify the query object though.
The easiest way to pare down a result set is with a query of queries which
lets you run SQL commands against a query object in memory. The trick is
you need to have a column that represents the row numbers for you. So,
assuming one of the columns in your resultset is rowNumber which contains
1 - 500, you could do this:
<cfquery dbtype="query" name="youOriginalQueryName">
SELECT *
FROM youOriginalQueryName
WHERE rowNumber > 50 and rowNumber <= 100
</cfquery>
or alternatively:
<cfquery dbtype="query" name="youOriginalQueryName" maxrows="50">
SELECT *
FROM youOriginalQueryName
WHERE rowNumber > 50
</cfquery>
Note, those cfquerys do NOT run anything against the database.
Alternatively, if you REALLY want, you can loop over the result set manually
and build a new result set with CF's query functions.
<cfscript>
qryNewQuery = queryNew(#qryOrigQuery.columnList#);
i = 0;
startRow = 1;
endRow = 50;
while (++i <= qryOrigQuery.recordcount)
{
if(i >= startRow && i < endRow)
{
queryAddRow(qryNewQuery,1);
c = 0;
while (++c <= listLen(qryOrigQuery.columnList))
{
QuerySetCell(qryNewQuery,
listGetAt(qryOrigQuery.columnList,c),
qryOrigQuery[listGetAt(qryOrigQuery.columnList,c)][i],
qryNewQuery.recordCount);
}
}
}
</cfscript>
----- Original Message -----
From: "Chad Gray" <[email protected]>
To: "cf-talk" <[email protected]>
Sent: Thursday, June 04, 2009 10:07 AM
Subject: reducing a query result
>
> Say I have a query returned with 500 records.
>
> Is there a way in CF (not SQL) to reduce the query to only rows 1-50,
> 51-100 etc?
>
> I talking physically reducing the query object.
>
>
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know
on the House of Fusion mailing lists
Archive:
http://www.houseoffusion.com/groups/cf-talk/message.cfm/messageid:323173
Subscription: http://www.houseoffusion.com/groups/cf-talk/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
