Yeah, I was thinking about that, but then it has to do a sub-query for every contact_id.... This might be what I have to end up doing, but I was curious if this could be done with a GROUP BY or something.
Thanks for the help. ....................... Ben Nadel Web Developer Nylon Technology 350 7th Avenue Floor 10 New York, NY 10001 212.691.1134 212.691.3477 fax www.nylontechnology.com "You know, like nunchuck skills, bowhunting skills, computer hacking skills... Girls only want boyfriends who have great skills." - Napoleon Dynamite -----Original Message----- From: Hua Wei [mailto:[EMAIL PROTECTED] Sent: Monday, June 05, 2006 10:15 AM To: CF-Talk Subject: RE: Querying for MAX of join table SELECT FROM tablename WHERE sort = (SELECT min(sort) FROM tablename ) -----Original Message----- From: Ben Nadel [mailto:[EMAIL PROTECTED] Sent: Monday, June 05, 2006 10:07 AM To: CF-Talk Subject: Querying for MAX of join table I have a table that is something like : + id + sort + contact_id + office_id This is a many-to-many join table joining contacts and office. What I would like to do is get a query of that that has only the record with the top SORT for each contact_id. So if I had the data: Id sort cotnact_id office_id ----- -------- ----------------- ------------ 1 1 40 1 2 1 57 1 3 2 57 2 4 1 63 5 I would end up with: Id sort cotnact_id office_id ----- -------- ----------------- ------------ 1 1 40 1 2 1 57 1 4 1 63 5 Getting rid of record (id:3) since it has sort 2 and I only want the top sort for contact (id:57). I know I can do stuff with sub queryies and what not, but I was hoping there was a more elegant solution with grouping or something. Thanks! ......................... Ben Nadel Web Developer Nylon Technology 350 7th Avenue Floor 10 New York, NY 10001 212.691.1134 212.691.3477 fax www.nylontechnology.com "You know, like nunchuck skills, bowhunting skills, computer hacking skills... Girls only want boyfriends who have great skills." - Napoleon Dynamite ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Message: http://www.houseoffusion.com/lists.cfm/link=i:4:242391 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
