Just add t_subcat_id to a GROUP BY statement:
<CFQUERY NAME="q_mostrecent" DATASOURCE="uspc">
SELECT MAX(t_mess) AS RECHIGH, t_subcat_ID
FROM t_msg
GROUP BY t_mess, t_subcat_id
</CFQUERY>
-----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
______________________________________________________________________
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/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
