Get the last identifier from duplicate records in a table

Question

so I have two tables, one of them is RAWtable, and the other is BASIC, I should get the last group identifier if there are several records (comparison of the same name, code). For example, I have this on a RAWtable:

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

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

 id groupid name code 2 G09161406 Name1 Code1 

This row is the only row to be inserted into the main table. If you return the last group identifier (groupid is a combination of date and time)

I tried this but did not work:

 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? Thank you very much.

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 

Or if you don't have 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 

Answer 2

Try this method, it will give you the maximum group id that will be the last:

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

Answer 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 max gorupid for all records containing more than one record in the table

Answer 4

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

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