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

Reply via email to