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

so i have two tables, one is RAWtable and the other is MAINtable, I have to get the latest groupID if there are more than one records exist (comparing same name, code). For example, I have this on RAWtable:

所以我有两个表，一个是RAWtable，另一个是MAINtable，如果存在多个记录（比较相同的名称，代码），我必须得到最新的groupID。例如，我在RAWtable上有这个：

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

the two records should be treated as one and should return this value only:

应将两个记录视为一个，并且只应返回此值：

id  groupid     name        code
2   G09161406   Name1       Code1

This row is the only row that shiuld be inserted in the main table. Provided returning the latest GroupID (the groupid is the combination of date and time)

此行是shiuld在主表中插入的唯一行。提供返回最新的GroupID（groupid是日期和时间的组合）

I've tried this but its not working:

我试过这个，但它不起作用：

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

how can i do this? thanks a lot.

我怎样才能做到这一点？非常感谢。

4 个解决方案

#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

Or if you don't have row_number()

或者如果你没有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

#2

Try this way, it will give you max group id which will be latest :

试试这种方式，它会给你最新的组ID：

SELECT MAX(GroupId), Name, Code 
FROM RAWtable
GROUP BY Name, Code

#3

select  max(id),name, code from RaTable
group by name,code having count(*)>1

Will return:

将返回：

id  name    code
2   Name1   Code1

Will return the max gorupid for all the records that have more than one record in the table

将为表中包含多条记录的所有记录返回max gorupid

#4

Try this: select max(t.groupid), t.name, t.code from RAWtable t group by t.name, t.code

试试这个：从t.name，t.code中选择来自RAWtable t group的max（t.groupid），t.name，t.code

This will basically select the max value of groupid for each name and code combination.

这基本上会为每个名称和代码组合选择groupid的最大值。

#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