Selecting max record for each user

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