The reason you are getting that error is because MAX is an aggregate function meaning that it takes all the results of the query and calculates one value for the column. You are trying to select multiple rows, but also have one of the columns be single value.
It won't let you do this. One thing you can do, and I'm not sure if it is appropriate here is to use GROUP BY this will allow you to specify the aggregate groups. So if you have something like this SELECT MAX(Price), OrderDate FROM orders GROUP BY OrderDate you would get the value of the highest priced order each day. So in order for you to have an Aggregate function in your SELECT statement all the other columns you are selecting must be in the GROUP BY ++++++++++++++++++++++++++++++++++++++++ Pete Freitag ([EMAIL PROTECTED]) CFDEV.COM ColdFusion Developer Resources http://www.cfdev.com/ -----Original Message----- From: Cozmo [mailto:[EMAIL PROTECTED]] Sent: Wednesday, October 17, 2001 11:55 AM To: CF-Talk Subject: CF_Query as an array I read in Ben Forte's book that the results of a query is actually an array. What I want to do is use array functions ArrayMax, ArrayMin to snag the min and max values of my query results. I know that this can be done with my query "MAX(Apartments.NUM_Bedrooms) AS Max_NUM_Bedrooms" , however the query (Access 2K) is this monster containing multiple inner joins (See below) . When I add a "MAX(Apartments.NUM_Bedrooms) AS Max_NUM_Bedrooms" to the query I get this error message: @@@@@@@@@@@@@ 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 'Apartment_ID' as part of an aggregate function. @@@@@@@@@@@@@ What I want to do is snag the min and max value for a sub set of data... Kinda like a the min and max values of of the results of a nested CF_Output. My question: How can I get these values, either with my query or snagging the values out of the query results using an array function.. Here is the catch. I don't want to hit the database again (Hence all the joins) and I do not want have to create an array from query results beacuase the are already in an array. Any takers? Thanx, Coz @@@@@@@ SELECT DISTINCT Apartments.Apartment_ID, Apartments.NUM_Bedrooms, Apartments.NUM_Bathrooms, Apartments.CUR_Price_Low, Apartments.CUR_Price_High, Apartments.NUM_Size, Apartments.TXT_Unit_Name, Apartments.NUM_Number_Available, Apartments.LKP_Availability_Status, Apartments.LKP_Communities_ID, Communities.Communities_ID, LKP_Availability_Status.TXT_Availability_Status, Communities.TXT_Communities_Name, Communities.TXT_Street_1, Communities.TXT_Street_2, Communities.TXT_City, Communities.LKP_State, TXT_Zip, MAX(Apartments.NUM_Bedrooms) AS Max_NUM_Bedrooms FROM Apartment_Amenities2 INNER JOIN ((Communities INNER JOIN (LKP_Availability_Status INNER JOIN Apartments ON LKP_Availability_Status.Availability_Status_ID = Apartments.LKP_Availability_Status) ON Communities.Communities_ID = Apartments.LKP_Communities_ID) INNER JOIN LKP_Apartment_Amenities ON Apartments.Apartment_ID = LKP_Apartment_Amenities.Apartment_ID) ON Apartment_Amenities2.Apartment_Amenity_ID = LKP_Apartment_Amenities.Apartment_Amenities_ID Cozmo <HoloGraphic>INC.</HoloGraphic> 522 E. Park Ave Tallahassee, Florida 32301 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Get the mailserver that powers this list at http://www.coolfusion.com 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
