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