Two levels of MAX in SQL Server

Question

I would like to get for each contract the record with the highest serial for the highest dates

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

Required results:

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

I achieved the results with two layers of self joins but may table is quite big and it takes a long time. Is there a more efficient way?

My Query:

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

user_1144035 · Answer

I would suggest window functions:

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;

user_9749424 · Answer

It should works for you:

SELECT 
     t.contact,
     MAX(t.C_DATE) C_DATE2,
     (SELECT MAX(SERIAL) FROM test t2 WHERE t2.contact = t.contact AND t2.C_DATE=MAX(t.C_DATE) LIMIT 1) SERIAL
     FROM test t
     GROUP BY 
         t.contact;

If I was you, definitely will define an index of contact, date, serial on my table as well.

Two levels of MAX in SQL Server

2 Answers