Selecting max record for each user
Solution 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
Solution 2
The safest way to do this is with row_number
select MemberId, ContractId, StartDate, EndDate
from (select mc.*,
row_number() over (partition by MemberId order by contractId desc) seqnum
from Member_Contracts mc
) mc
where seqnum = 1
This handles the case of multiple contracts for the same member . . . which may not really be an issue in this data.
Scott
Updated on April 03, 2020Comments
-
Scott almost 4 years
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.
-
Lamak over 11 yearsNice, I was writing an answer that used
ROW_NUMBER
, but this is simpler and will work on versions for SQL Server previous to 2005 -
Ar5hv1r over 9 yearsAwesome, this seems like the more general-case solution than relying on the contract ID being unique. Any chance you'd mind elaborating on what your query is doing here? In my case, I want to select the most recent record (as the title suggests); I put it into an SQLFiddle to demonstrate the most-recent behavior.