It goes away but it won't give me what I want - I effectively want one group, that would give me as many groups as there are unique tiptitle values ?
At 04:22 PM 2/22/2002, you wrote: >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 > > > ______________________________________________________________________ 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=coldfusiona 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
