Whenever you use aggregate functions in SQL, you need to add a group by clause that contains the fields that you're not aggregating on. That would be stuff in your select and in the order by. So, add a "group by Tip Title" before the order by and your error should go away.
-----Original Message----- From: James Sleeman [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 21, 2002 8:01 PM To: CF-Talk Subject: SQL trickyness Hi all, trying to do a little funky SQL to get the minimum and maxim um ID's with respect to a sort order on a table but I'm getting an acces s error, cam anybody see what is wrong with my sql here... <CFQUERY DATASOURCE="#CFG.DS#" CONNECTSTRING="#CFG.CONNECTSTRING# " DBTYPE="#CFG.DBTYPE#" NAME="Q_Tips"> SELECT Last(TIPID) AS MaximumTIP, First(TIPID) AS MinimumTIP FROM TIPS WHERE TipTipCategoryLink = #Q_Tip.TipTipCategoryLi nk# ORDER BY TipTitle ASC </CFQUERY> Error Diagnostic Information ODBC Error Code = S1000 (General error) [Microsoft][ODBC Microsoft Access Driver] You tried to execute a quer y that does not include the specified expression 'TipTitle' as part of an aggregate function. SQL = "SELECT Last(TIPID) AS MaximumTIP, First(TIPID) AS MinimumTIP FROM TIPS WHERE TipTipCategoryLink = 1 ORDER BY TipTitle ASC" Data Source = "" The error occurred while processing an element with a general identif ier of (CFQUERY), occupying document position (20:2) to (20:104) in the temp late file D:\JOBS\WEB-FUSION\WEBSHOPCODEBASE\_MIRROREDFILES\BFD5C5FB051709B72C9 FF321B6 2A465C\PLUGIN_SHOWSTUFF\QRY_SHOWTIP.CFM. Date/Time: 02/22/02 16:02:18 Browser: Mozilla/4.0 (compatible; MSIE 5.5; Windows NT 5.0) Remote Address: 10.10.10.105 Query String: FuseAction=showTip&TipID=7&CFID=6778&CFTOKEN=31983074&ForceUp date=1 James Sleeman Innovative Media Ltd Phone: (03) 377 6262 http://www.websolutions.co.nz/ CAUTION: The information contained in this email message is confident ial and may be legally privileged. If the reader of this message is not t he intended recipient you are notified that any use, dissemination, distribution or reproduction of this message is prohibited. If you ha ve received this message in error please notify the sender immediately a nd destroy the original message and any attachments. Views expressed in this communication may not be those of Innovative Media Ltd. _____________________________________________________________________ _ Get Your Own Dedicated Windows 2000 Server PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation � $99/Month � Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionb FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists ______________________________________________________________________ Why Share? Dedicated Win 2000 Server � PIII 800 / 256 MB RAM / 40 GB HD / 20 GB MO/XFER Instant Activation � $99/Month � Free Setup http://www.pennyhost.com/redirect.cfm?adcode=coldfusionc FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
