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
#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