Thanks Larry, I'm only querying about 4k records for this project, so the query is rather quick. But I will definitely snippet the code for future projects. BTW, would you have a string snippet for capitalizing the first letter of each work in a title output?
ex. "the brown dog" - converted to "The Brown Dog". On Tue, Jul 24, 2012 at 7:57 AM, Larry Morrow <[email protected]> wrote: > 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 > 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**** > > **** > > 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> > ------------------------------------------------------------- **** > > > __________ 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 Marketing Solutions with vision! http://www.singleconcepts.com
