使用最新日期对t-sql进行分组

I have a table like this

我有一张这样的桌子

Event ID | Contract ID | Event date | Amount |
----------------------------------------------
1        | 1           | 2009-01-01 |    100 |
2        | 1           | 2009-01-02 |     20 |
3        | 1           | 2009-01-03 |     50 |
4        | 2           | 2009-01-01 |     80 |
5        | 2           | 2009-01-04 |     30 |

For each contract I need to fetch the latest event and amount associated with the event and get something like this

对于每个合同,我需要获取与事件相关的最新事件和金额,并获得类似的结果

Event ID | Contract ID | Event date | Amount |
----------------------------------------------
3        | 1           | 2009-01-03 |     50 |
5        | 2           | 2009-01-04 |     30 |

I can't figure out how to group the data correctly. Any ideas?

我无法弄清楚如何正确分组数据。有任何想法吗?

Thanks in advance.

提前致谢。

1 个解决方案

#1

SQL 2k5/2k8:

with cte_ranked as (
 select *
    , row_number() over (
         partition by ContractId order by EvantDate desc) as [rank]
    from [table])
select * 
   from cte_ranked
   where [rank] = 1;

SQL 2k:

 select t.*
    from table as t
    join (
        select max(EventDate) as MaxDate
            , ContractId 
            from table 
            group by ContractId) as mt
       on t.ContractId = mt.ContractId
          and t.EventDate = mt.MaxDate