4

How would I select a specific range of rows using a query of queries?

e.g

<cfquery name="myQuery" maxrows ="20" startrow="12">
 SELECT *
 FROM   previous_query
 WHERE  row_numer >= 12
</cfquery>

that sort of thing...

Dpolehonski
  • 948
  • 6
  • 11

2 Answers2

9

This was a tricky one but your problem intrigued me. I think I may have a solution

I wrote a function that delete everything prior to the rows you want and then deletes everything after the rows you want.

the function rowrange() takes 3 parameters. 1. the queryname you are working with. 2. the starting row you want 3. the number of rows you want.

UPDATED: My friend John Whish pointed out that I actually do not need to do the looping to get this to work. Removing the loops makes this much more scalable.

<cfquery name="myQuery">
 SELECT *
 FROM   previous_query
 WHERE  row_numer >= 12
</cfquery>


<cfset  rowRange(myQuery,7,4)>
<cfdump var="#myQuery#">

<cffunction name="rowRange" hint="return a range of rows from a given query">
    <cfargument name="qObj" type="query" required="true">
    <cfargument name="start" type="numeric" required="true" default="1" hint="The number of the first row to include">
    <cfargument name="range" type="numeric" required="true" default="1" hint="The number of rows">


    <cfset var i = arguments.start+arguments.range-1>
    <cfset arguments.qObj.removeRows(i,arguments.qObj.recordcount-i)>
    <cfset arguments.qObj.removeRows(0,arguments.start-1)>

    <cfreturn arguments.qObj>
</cffunction>
Tim Cunningham
  • 329
  • 1
  • 7
  • Its a very good solution, and one i'll be able to implement in multiple locations. Thank you – Dpolehonski Jul 12 '12 at 09:22
  • UPDATED THE CODE: My friend John Whish pointed out that I actually do not need to do the looping to get this to work. Removing the loops makes this much more scalable. – Tim Cunningham Jul 12 '12 at 20:33
  • I met John whish as CF.objective() this year. He told me i should migrate the client onto SQL server. I wish I could, I will be adding this function to my library of ms Access hacks. Thanks. – Dpolehonski Jul 13 '12 at 08:57
  • Wow so you are the guy still using Access ;) No worries, our company started with that too. – Tim Cunningham Jul 13 '12 at 13:16
0

Doing this natively in CF isn't supported, so you'll have to add a column in your original record set to do the counting for you.

SELECT ..., row_num AS Counter

Row_Num may vary based on your DBMS.

Busches
  • 1,964
  • 16
  • 19
  • I would have done that but Ms Access doesn't support any row_num type functionality. I simulated it by using queryAddColumn and manually adding a rownumber column before doing the QoQ. – Dpolehonski Jul 12 '12 at 09:24
  • Well that's not quite true, you can easily manipulate the query http://www.cwithb.com/2010/09/simplified-take-on-an-access-database-query-sql-row_numberranking-equivalent/ – Busches Jul 13 '12 at 19:47
  • 1
    That may be true, however as I have found with my testing running sub-queries on access databases with large datasets becomes exponentialy innefficient.. its faster to do it manually in CF – Dpolehonski Jul 16 '12 at 07:53