选择每个用户的最大记录

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.

我有一个member_contracts表具有以下（简化）结构。

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.

基于每个用户执行此操作非常简单，因为我可以使用子查询来为指定用户选择max contractID。我正在使用SQL服务器，所以如果有一种特殊的方式来做这种味道，我愿意使用它。就个人而言，我喜欢与引擎无关的东西。

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.

编辑：我还应该补充一点，我正在寻找每个用户的最大contractID值，而不是最近的日期。

2 个解决方案

#1

This solution uses the uniqueness of the ContractId field:

此解决方案使用ContractId字段的唯一性：

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

看到它在线工作：sqlfiddle

#2

The safest way to do this is with row_number

最安全的方法是使用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.

这处理同一成员的多个合同的情况。。。这可能不是这个数据中的一个问题。

#1