That worked - thanks. Mark
-----Original Message----- From: Pascal Peters [mailto:[EMAIL PROTECTED]] Sent: Monday, January 21, 2002 10:12 AM To: CF-Talk Subject: RE: Query error in aggregate function SELECT t_mess, t_subcat_ID FROM t_msg WHERE t_mess IN (SELECT MAX(t_mess) FROM t_msg) -----Original Message----- From: Mark Leder [mailto:[EMAIL PROTECTED]] Sent: maandag 21 januari 2002 15:56 To: CF-Talk Subject: RE: Query error in aggregate function I still have been unable to get this to work, using either method. The Group by brings me the highest number of t_subcat_ID (potential values of 1 thru 9), rather than the t_mess highest value (currently 86). The value of t_subcat_ID is not from the same record as the t_mess highest value. The values output should be, in this instance, 86 and 1, not 9 and 9 (referring to the lowest record, not the highest. Doing a subquery throws an error message about an invalid aggregate function expression. For clarity, here's some db values: t_mess (autonumbered primary key) 84, 85, 86 t_subcat_ID 9, 4, 1 So, if 85 were the most recent record, I would want 85 and 4. Thanks for your assistance. Mark -----Original Message----- From: Steve Oliver [mailto:[EMAIL PROTECTED]] Sent: Monday, January 21, 2002 8:54 AM To: CF-Talk Subject: RE: Query error in aggregate function You could either use the GROUP BY statement: SELECT MAX(t_mess) AS RECHIGH, t_subcat_ID FROM t_msg GROUP BY t_mess, t_subcat_id or do a subquery: SELECT t_subcat_ID, (SELECT MAX(t_mess) FROM t_msg) AS RECHIGH FROM t_msg ______________________ steve oliver cresco technologies, inc. http://www.crescotech.com -----Original Message----- From: Mark Leder [mailto:[EMAIL PROTECTED]] Sent: Monday, January 21, 2002 8:36 AM To: CF-Talk Subject: Query error in aggregate function I want to run a query (using Access) which will return the most recent record ID # (t_mess) and one other column (t_subcat_ID). When I run it without the t_subcat_ID field, it runs great, bringing me the most recent record. When I add t_subcat_ID, I receive the following error: ------------------------------- ODBC Error Code = 37000 (Syntax error or access violation) [Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 't_subcat_ID' as part of an aggregate function. The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (30:1) to (33:39). ------------------------------- And here's the query expression: <CFQUERY NAME="q_mostrecent" DATASOURCE="uspc"> SELECT MAX(t_mess) AS RECHIGH, t_subcat_ID FROM t_msg </CFQUERY> <cfoutput>#q_mostrecent.rechigh# #q_mostrecent.t_subcat_ID#</cfoutput><cfabort> -------------------------------- How do I fix this? Thanks in advance. Mark ______________________________________________________________________ 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/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
