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.
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
__________ 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
-------------------------------------------------------------
--
Michael Brown
770-605-5240
Marketing Solutions with vision!
-------------------------------------------------------------
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
-------------------------------------------------------------
|
