Thanks Charlie,
I was waiting on a reply from Michael to put this something very similar to this together as a suggestion. The second reason this is what I would do is that it is not dependent on the database as not all databases may not support some limits during the query. Lastly if Michael wants to change the number of records he can do it in the call and not hardcode it in the call to SQL. Larry J. Morrow COO & CTO A-PLUS Community Solutions, Inc. Office: 678.534.8326 ext 100 Fax: 801.843.5832 Cell: 678.416.8032 www.a-plus.net Please Note: The information in this email contains privileged, confidential, and proprietary information and is otherwise protected from disclosure. If you have received this email in error, please notify A-PLUS Community Solutions, Inc. immediately by replying to this message and deleting it from your computer. Anyone other than the intended is prohibited from reading, copying, or distributing this transmission. From: [email protected] [mailto:[email protected]] On Behalf Of Charlie Arehart Sent: Tuesday, July 24, 2012 1:32 AM To: [email protected] Subject: RE: [ACFUG Discuss] Have a question about group limits Frank, are you aware of the CFOUTPUT GROUP attribute? That would eliminate the need to do the 2 queries below. Many miss it, though it's indeed a valuable feature to create just these sort of "control break" reports. I'll show an example in a moment. And Michael, are you saying that you ARE trying to use the GROUP attribute on CFOUTPUT, but you're wanting to limit the inner loop? Sadly, it does NOT accept a Maxrows on it, so you can't do that, but I have another solution you could use. (The answer is NOT adding a maxrows to the outer cfoutput loop, as that instead limits how many of the outer loop values to show, which is not what you want.) First, here's a basic cfoutput group demo, for those not familiar with it. It's a classic "control break" report, in this case produce a report, by city, of the artists and their art, as found in the demo artgallery datasource that comes with CF8 and above: <cfquery datasource="cfartgallery" name="getArt"> SELECT * FROM art a, artists t WHERE a.artistID = t.artistID ORDER BY city asc </cfquery> <cfoutput query="getArt" group="city"> <h2>#city#</h2> <cfoutput> #artname# (<em>by #firstname# #lastname#</em>) #DollarFormat(price)#<br> </cfoutput> </cfoutput> The output (first two groups) would look like this: Atlanta Beauty (by Raquel Young) $100,000.00 Mom (by Raquel Young) $85,000.00 Mountains (by Raquel Young) $150,000.00 Paradise (by Raquel Young) $300,000.00 Mystery (by Raquel Young) $250,000.00 Berkeley Do it (by Austin Weber) $30,000.00 You Don't Know Me (by Austin Weber) $42,700.00 Prize Fight (by Austin Weber) $25,000.00 Toxic (by Austin Weber) $22,000.00 1958 (by Austin Weber) $75,000.00 But now let's say we wanted no more than 3 of these to be shown per city. While we can't add maxrows to the inner loop (inner cfoutput), we can just keep track of the count ourselves, and only show those we want. <cfoutput query="getArt" group="city"> <h2>#city#</h2> <cfset ct=0> <cfoutput> <cfif ct++ lt 3> #artname# (<em>by #firstname# #lastname#</em>) #DollarFormat(price)#<br> </cfif> </cfoutput> </cfoutput> Some may note that this DOES still loop over all the inner loop results, but there's just no way to stop that. If this were a real CFLOOP, we could use the cfbreak tag to "jump out of the inner loop", but it does not work in CFOUTPUT loops (would be nice). Now, some may note that a change in CF10 allows CFLOOP query loops to use the GROUP attribute, so one could use the CFBREAK then. Let us know if this helps, Michael (and Frank). /charlie From: [email protected] [mailto:[email protected]] On Behalf Of Frank Moorman Sent: Monday, July 23, 2012 6:07 PM To: [email protected] Cc: Michael Brown Subject: Re: [ACFUG Discuss] Have a question about group limits Michael, I am not sure, but I think this is what you are looking for... I do not believe that MySQL supports the LIMIT clause in a subquery, so I believe the following is easiest without creating a stored procedure or a unreadable monstrosity of SQL... <cfquery name="groups" datasource="xxx"> SELECT DISTINCT dest FROM tableB WHERE top10 = "1"; </cfquery> <cfloop query="groups"> <cfquery name="top10" datasource="xxx"> SELECT dest, property, url_e, price, description FROM tableB WHERE top10 = "1" AND dest = <cfqueryparam value="#groups.dest#"> LIMIT 10 </cfquery> <!--- Either print here, or combine in one large query, using QueryNew() outside the loop and QueryAddRow() for each result ---> </cfloop> You definitely want to use the CFQUERYPARAM in the second query. Even though the dest field is coming straight from the database and probably does not need validation, it turns the SQL into a prepared SQL for the database so that MySQL will cache the execution plan and save a little time when it is executed multiple times. Let me know if that is what you wanted... Frank On 07/23/2012 05:28 PM, Michael Brown wrote: Thanks Larry, Sorry for not being clear. I want to limit the number records displayed "per" group. In the database there are thousands of records, but I just want to display 10 records per group. When I run the query, it only displays 10 records for only 1 group in the "dest" field. Hope that's a little clearer. THANKS On Mon, Jul 23, 2012 at 5:14 PM, Larry Morrow <[email protected]> wrote: Hi Michael, I may be the first and the least capable of helping, but I did not quite understand what you are trying to accomplish. Your query as is will list the results so that the records will be sorted by the group. When you say limit, can you provide a description of a results you are looking to achieve. Hopefully, I was clear in my question. Larry J. Morrow COO & CTO A-PLUS Community Solutions, Inc. Office: 678.534.8326 ext 100 <tel:678.534.8326%20ext%20100> Fax: 801.843.5832 Cell: 678.416.8032 www.a-plus.net Please Note: The information in this email contains privileged, confidential, and proprietary information and is otherwise protected from disclosure. If you have received this email in error, please notify A-PLUS Community Solutions, Inc. immediately by replying to this message and deleting it from your computer. Anyone other than the intended is prohibited from reading, copying, or distributing this transmission. From: [email protected] [mailto:[email protected]] On Behalf Of Michael Brown Sent: Monday, July 23, 2012 5:05 PM To: [email protected] Subject: [ACFUG Discuss] Have a question about group limits Hello fellow group members, I can't seem to get my output to limit the number of records displayed per group in the database. I using mysql with cf9. This is my query that I'm attempting to get data from. The group field is "dest". <cfquery name="GetData" datasource="xxx_mysql" > select dest, property, url_e, price, description from tableB where top10 = "1" order by dest </cfquery> Thanks! -- Michael Brown 770-605-5240 Marketing Solutions with vision! http://www.singleconcepts.com __________ Information from ESET NOD32 Antivirus, version of virus signature database 7323 (20120723) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com ------------------------------------------------------------- To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink <http://www.fusionlink.com> ------------------------------------------------------------- -- Michael Brown 770-605-5240 <http://singleconcepts.com/images/newlogo2011_email.jpg> Marketing Solutions with vision! http://www.singleconcepts.com ------------------------------------------------------------- To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com ------------------------------------------------------------- __________ Information from ESET NOD32 Antivirus, version of virus signature database 7323 (20120723) __________ The message was checked by ESET NOD32 Antivirus. http://www.eset.com ------------------------------------------------------------- To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by FusionLink <http://www.fusionlink.com> ------------------------------------------------------------- ------------------------------------------------------------- To unsubscribe from this list, manage your profile @ http://www.acfug.org?fa=login.edituserform For more info, see http://www.acfug.org/mailinglists Archive @ http://www.mail-archive.com/discussion%40acfug.org/ List hosted by http://www.fusionlink.com -------------------------------------------------------------
