I'm not sure if I should know the answer to this one or not but I thought I'd post the problem and see what you think i've got two tables security, memberships they can be joined based on contact_id fields, memberships has a foreign key group_id from the groups table, each contact in the security table has a corresponding row in the memberships table and they go together like this such as security.time_stamp security.contact_id memberships.contact_id memberships.group_id 2009-03-01 c1 c1 g1 2009-03-01 c2 c2 g2 2009-03-20 c3 c3 g1 2009-03-01 c4 c4 g3 Code (markup): *** so here is the actual question I guess what I want to do is to create a query that will return one contact_id for each group in the memberships table.. and I want that contact to be chosen based on their time_stamp (return the contact with the most recent time_stamp) i.e. if the query was run on the above data I would get c2, c3 and c4 as my result set (c1 is not included because c3 is in the same group and has a more recent time_stamp) can anyone think of a query for this?
SELECT security.time_stamp, security.contact_id, memberships.contact_id, memberships.group_id FROM security LEFT OUTER JOIN memberships ON security.contact_id = memberships.contact_id GROUP BY memberships.group_id ORDER BY security.time_stamp DESC This should work with SQL server.