Thank you Greg Morphis wrote: > You need a group by in your query.. > >> SELECT count(DISTINCT rate) as rateCount, rate >> FROM myrates >> WHERE my_code = 385 and year = 2005 >> > GROUP BY rate > >> ORDER BY rate >> > > > > > On 4/3/07, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > >> Why do the first two queries work and the last one fail? The only difference >> is adding the comma and additional field to the SELECT statement. >> >> <cfquery datasource="myDSN" name="Rates"> >> SELECT count(DISTINCT rate) as rateCount >> FROM myrates >> WHERE my_code = 385 and year = 2005 >> ORDER BY rate >> </cfquery> >> >> <cfquery datasource="myDSN" name="Rates"> >> SELECT rate >> FROM myrates >> WHERE my_code = 385 and year = 2005 >> ORDER BY rate >> </cfquery> >> >> >> FAIL >> >> <cfquery datasource="myDSN" name="Rates"> >> SELECT count(DISTINCT rate) as rateCount, rate >> FROM myrates >> WHERE my_code = 385 and year = 2005 >> ORDER BY rate >> </cfquery> >> >> >> >> >> > >
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Upgrade to Adobe ColdFusion MX7 The most significant release in over 10 years. Upgrade & see new features. http://www.adobe.com/products/coldfusion?sdid=RVJR Archive: http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:274479 Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4