sql - 从表中的重复记录中获取最新 ID

Question

所以我有两个表，一个是RAWtable，另一个是MAINtable，我必须获取最新的groupID(如果有)是否存在多个记录(比较相同的名称、代码)。例如，我在 RAWtable 上有这个:

id  groupid     name        code
1   G09161405   Name1       Code1
2   G09161406   Name1       Code1

这两条记录应被视为一条记录，并且应仅返回此值:

id  groupid     name        code
2   G09161406   Name1       Code1

此行是唯一应插入到主表中的行。提供返回最新的GroupID(groupid是日期和时间的组合)

我已经尝试过这个，但它不起作用:

SELECT MAST.ID, MAST.code, MAST.name FROM RAWtable AS MAST INNER JOIN 
(SELECT code, name, grouid,id FROM RAWtable AS DUPT GROUP BY code, name, groupid,id HAVING COUNT(*) >= 2) DUPT
 ON  DUPT.code =MAST.code and DUPT.name =MAST.name where dupt.groupid >mast.groupid 

我该怎么做？非常感谢。

Answer 1

select R.id,
       R.groupid,
       R.name,
       R.code
from (select id, 
             groupid, 
             name, 
             code,
             row_number() over(partition by name, code order by groupid desc) as rn
      from RawTable       
     ) as R
where R.rn = 1     

或者如果您没有 row_number()

select R1.id,
       R1.groupid,
       R1.name,
       R1.code
from RawTable as R1
  inner join (  
              select name, code, max(groupid) as groupid
              from RawTable
              group by name, code
             ) as R2
    on R1.name = R2.name and
       R1.code = R2.code and
       R1.groupid = R2.groupid