This is one solution & probably fine for a small # of groups. I would do this in a stored proc in the database if at all possible so if your dataset (i.e. # of groups) got large you're still limiting the output w/o running a large number of queries (which can itself cause headaches).
On Mon, Jul 23, 2012 at 6:06 PM, Frank Moorman <[email protected]>wrote: > 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 <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 > > 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 > ------------------------------------------------------------- > -- Dawn
