You'll probably have to do some subqueries to get the min, max and id values first, then get the tiptitle.
-----Original Message----- From: James Sleeman [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 21, 2002 8:31 PM To: CF-Talk Subject: RE: SQL trickyness It goes away but it won't give me what I want - I effectively want on e 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. Tha t > would >be stuff in your select and in the order by. So, add a "group by Ti p >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 max im >um >ID's with respect to a sort order on a table but I'm getting an acce s >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 MinimumT IP > FROM TIPS > WHERE TipTipCategoryLink = #Q_Tip.TipTipCategory Li >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 que r >y that >does not include the specified expression 'TipTitle' as part of an >aggregate function. >SQL = "SELECT Last(TIPID) AS MaximumTIP, First(TIPID) AS MinimumTI P > FROM >TIPS WHERE TipTipCategoryLink = 1 ORDER BY TipTitle ASC" >Data Source = "" >The error occurred while processing an element with a general identi f >ier of >(CFQUERY), occupying document position (20:2) to (20:104) in the tem p >late >file >D:\JOBS\WEB-FUSION\WEBSHOPCODEBASE\_MIRROREDFILES\BFD5C5FB051709B72C 9 >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&ForceU p >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 confiden t >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 h a >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 ______________________________________________________________________ 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
