sql - SQL Server中MAX的两个级别

Question

我想为每个契约(Contract)获得最高日期最高序列的记录

ID  CONTRCT  C_DATE       SERIAL
--------------------------------
1   ABC      20201201      1
2   ABC      20201201      2
3   ABC      20201201      3
4   DEF      20201201      3
4   DEF      20201210      1
5   DEF      20201210      2

要求的结果:

ID  CONTRCT   C_DATE    SER
3   ABC       20201201  3
6   DEF       20201210  2

我用两层自连接实现了结果，但可能表很大并且需要很长时间。
有没有更有效的方法？
我的查询:

SELECT t3.ID
    ,t3.CONTRCT
    ,t3.C_DATE
    ,t3.SER
FROM (
    SELECT ID
        ,tbl.CONTRCT
        ,tbl.C_DATE
        ,tbl.SER
    FROM (
        SELECT CONTRCT
            ,C_DATE
            ,max(SER) mx
        FROM tbl
        GROUP BY CONTRCT
            ,C_DATE
        ) t1
    JOIN tbl ON t1.C_DATE = tbl.C_DATE
        AND t1.mx = tbl.SER
        AND t1.CONTRCT = tbl.CONTRCT
    ) t3
JOIN (
    SELECT CONTRCT
        ,MAX(C_DATE) MAX_DATE
    FROM (
        SELECT ID
            ,tbl.CONTRCT
            ,tbl.C_DATE
            ,tbl.SER
        FROM (
            SELECT CONTRCT
                ,C_DATE
                ,max(SER) mx
            FROM tbl
            GROUP BY CONTRCT
                ,C_DATE
            ) t1
        JOIN tbl ON t1.C_DATE = tbl.C_DATE
            AND t1.mx = tbl.SER
            AND t1.CONTRCT = tbl.CONTRCT
        ) t2
    GROUP BY CONTRCT
    ) t4 ON t4.CONTRCT = t3.CONTRCT
    AND t4.MAX_DATE = t3.C_DATE

Answer 1

我建议窗口函数:

select t.*
from (select t.*,
             row_number() over (partition by contract order by date desc, ser desc) as seqnum
      from tbl t
     ) t
where seqnum = 1;