Did you find the answer yet? Why not shift the group by classid to the outside query? do you really need all the fields from 3 tables?
-----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, March 13, 2003 4:49 PM To: CF-Talk Subject: RE: subquery and group by a non selected field Yes, that is the problem. I do have to have class_id in the SELECT clause, however in order for it to work with the main query the subquery cannot return multiple columns. Here is where my problem lies. How do I group by a column that I cannot have in my SELECT clause? DM = = = Original message = = = 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 Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
