Don't you also need to have class_Id in your select clause? what error are you getting when you just run the subquery by itself?
-----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, March 13, 2003 4:21 PM To: CF-Talk Subject: SQL: subquery and group by a non selected field I am trying to return a list of ids in a sub query but have them grouped by another column. View the subquery below. I am attempting to GROUP BY class_id because I need 1 session_id (the one with the MAX(endDate)) from each group of class_ids. SELECT * ~FROM courses A, classes B,sessions C ~WHERE A.course_id = B.course_id AND ~~ C.class_id = B.class_id AND ~C.session_id IN (SELECT session_id ~~~~FROM sessions ~~~~GROUP BY class_id ~~~~HAVING MAX(endDate) BETWEEN '#url.minDate#' AND '#url.maxDate#') Any ideas on how to get this to work? DM ___________________________________________________________ Sent by ePrompter, the premier email notification software. Free download at http://www.ePrompter.com. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Structure your ColdFusion code with Fusebox. Get the official book at http://www.fusionauthority.com/bkinfo.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
