Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Get Latest ID from a Duplicate Records in a table

Tags:

sql

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:

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)

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.

like image 873
Blair Yumi Avatar asked Sep 16 '11 06:09

Blair Yumi


People also ask

How do I get the latest ID in SQL?

We can use the ORDER BY statement and LIMT clause to extract the last data. The basic idea is to sort the sort the table in descending order and then we will limit the number of rows to 1. In this way, we will get the output as the last row of the table. And then we can select the entry which we want to retrieve.

How do you find duplicate record in a table?

One way to find duplicate records from the table is the GROUP BY statement. The GROUP BY statement in SQL is used to arrange identical data into groups with the help of some functions. i.e if a particular column has the same values in different rows then it will arrange these rows in a group.


3 Answers

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
like image 124
Mikael Eriksson Avatar answered Oct 25 '22 03:10

Mikael Eriksson


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

SELECT MAX(GroupId), Name, Code 
FROM RAWtable
GROUP BY Name, Code 
like image 34
Upendra Chaudhari Avatar answered Oct 25 '22 02:10

Upendra Chaudhari


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

like image 24
Icarus Avatar answered Oct 25 '22 03:10

Icarus