Selecting max record for each user
This solution uses the uniqueness of the ContractId field:
SELECT MemberID, ContractID, StartDate, EndDateFROM member_contracts WHERE ContractId IN ( SELECT MAX(ContractId) FROM member_contracts GROUP BY MemberId)See it working online: sqlfiddle
The safest way to do this is with row_number
select MemberId, ContractId, StartDate, EndDatefrom (select mc.*, row_number() over (partition by MemberId order by contractId desc) seqnum from Member_Contracts mc ) mcwhere seqnum = 1This handles the case of multiple contracts for the same member . . . which may not really be an issue in this data.