Selecting max record for each user

58,328

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.

Share:
58,328
Scott
Author by

Scott

Updated on April 03, 2020

Comments

  • Scott
    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
    Lamak over 11 years
    Nice, 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
    Ar5hv1r over 9 years
    Awesome, 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.