Joe, Yeah, I think that would be the best thing to go with. My only concern would be data integrity at that point. There might be a record here and there that have a "zero" sort as I think 0 was the default column value... But I will have to check on that. But thanks, I didn't think to try that :)
....................... 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: Joe Rinehart [mailto:[EMAIL PROTECTED] Sent: Monday, June 05, 2006 10:19 AM To: CF-Talk Subject: Re: Querying for MAX of join table Hey Ben, Why wouldn't WHERE sort = 1 work? -Joe On 6/5/06, Ben Nadel <[EMAIL PROTECTED]> wrote: > 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:242400 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
