James, Rather than using Last() and First(), try Max() and Min(). Also, try adding in a GROUP BY clause. Adding it in always seems to fix the error messages about aggregate functions.
-Brent > -----Original Message----- > From: James Sleeman [mailto:[EMAIL PROTECTED]] > Sent: Thursday, February 21, 2002 7:01 PM > To: CF-Talk > Subject: SQL trickyness > > > Hi all, > trying to do a little funky SQL to get the minimum > and maximum > ID's with respect to a sort order on a table but I'm getting > an access > 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.TipTipCategoryLink# > ORDER BY TipTitle ASC > </CFQUERY> > > > > Error Diagnostic Information > ODBC Error Code = S1000 (General error) > [Microsoft][ODBC Microsoft Access Driver] You tried to > execute a query 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 > identifier of > (CFQUERY), occupying document position (20:2) to (20:104) in > the template > file > D:\JOBS\WEB-FUSION\WEBSHOPCODEBASE\_MIRROREDFILES\BFD5C5FB0517 > 09B72C9FF321B62A465C\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&ForceUpdate=1 > > > > > James Sleeman > Innovative Media Ltd > Phone: (03) 377 6262 > http://www.websolutions.co.nz/ > > CAUTION: The information contained in this email message is > confidential > and may be legally privileged. If the reader of this message > is not the > intended recipient you are notified that any use, dissemination, > distribution or reproduction of this message is prohibited. > If you have > received this message in error please notify the sender > immediately and > 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
