Find Duplicate Rows in MySQL

Duplicates are nothing but, repeated data of database objects(table,view). We can find duplicates in different ways.

Following table named ’emp1′ contains duplicates in the column empno and empname

+-------+-----------+ | empno | empname | +-------+-----------+ | 1000 | Karunakar | | 1001 | Kalpesh | | 1002 | NULL | | 1003 | Steve | | 1003 | James | | 1003 | James | | 1004 | Pearson | | 1004 | Pearson | +-------+-----------+

Find duplicates in MySQL table emp1 with column named empno

select e.*, b.totalcount as dup from emp1 e inner join ( select empno, count(*) totalcount from emp1 group by empno) b on e.empno=b.empno where b.totalcount>=2;

Output

+-------+---------+-----+ | empno | empname | dup | +-------+---------+-----+ | 1003 | Steve | 3 | | 1003 | James | 3 | | 1003 | James | 3 | | 1004 | Pearson | 2 | | 1004 | Pearson | 2 | +-------+---------+-----+

Find duplicates in MySQL table emp1 with column named empname

select e.*, b.totalcount as dup from emp1 e inner join ( select empname, count(*) totalcount from emp1 group by empname) b on e.empname=b.empname where b.totalcount>=2;

Ouput:

+-------+---------+-----+ | empno | empname | dup | +-------+---------+-----+ | 1003 | James | 2 | | 1003 | James | 2 | | 1004 | Pearson | 2 | | 1004 | Pearson | 2 | +-------+---------+-----+

CheapSexCams
Responses are currently closed, but you can trackback from your own site.

Comments are closed.

Powered by k2schools
%d bloggers like this: