Selecting max record for each user

Question

This seems if it should be fairly simple, but I'm stumbling in trying to find a solution that works for me.

I have a member_contracts table that has the following (simplified) structure.

MemberID | ContractID   | StartDate | End Date | ------------------------------------------------ 1          1              2/1/2002    2/1/2003 2          2              3/1/2002    3/1/2003 3          3              4/1/2002    4/1/2003 1          4              2/1/2002    2/1/2004 2          5              3/1/2003    2/1/2004 3          6              4/1/2003    2/1/2004 

I'm trying to create a query that will select the most recent contracts from this table. That being the following output for this small example:

MemberID | ContractID   | StartDate | End Date | ------------------------------------------------ 1          4              2/1/2002    2/1/2004 2          5              3/1/2003    2/1/2004 3          6              4/1/2003    2/1/2004 

Doing this on a per-user basis is extremely simple since I can just use a subquery to select the max contractID for the specified user. I am using SQL server, so if there's a special way of doing it with that flavor, I'm open to using it. Personally, I'd like something that was engine agnostic.

But, how would I go about writing a query that would accomplish the goal for all the users?

EDIT: I should also add that I'm looking for the max contractID value for each user, not the most recent dates.

Answer 1

This solution uses the uniqueness of the ContractId field:

SELECT MemberID, ContractID, StartDate, EndDate FROM member_contracts  WHERE ContractId IN (     SELECT MAX(ContractId)     FROM member_contracts      GROUP BY MemberId ) 

See it working online: sqlfiddle