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 ______________________________________________________________________ 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/cf-talk@houseoffusion.com/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
