Hello Friend,

We have created a table named 'employee'.

CREATE TABLE `employee` (               
            `id` bigint(255) default NULL,        
            `name` varchar(255) default NULL,     
            `address` varchar(255) default NULL   
          )

In this table,we have entered following data:

1 A Delhi

1 B Mumbai

2 C Delhi

3 D Delhi

Then we have used the following query to find the duplicate data:

SELECT DISTINCT t1.id, t1.name,t1.address FROM employee t1, employee t2 WHERE t1.id= t2.id AND t1.name <> t2.name AND t1.address <> t2.address;

Hope that it will be helpful for you.

Thanks

Hi,

Suppose you have following dataset:

CREATE TABLE `employee` (
  `id` bigint(255) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL,
  `address` varchar(255) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

/*Data for the table `employee` */

insert  into `employee`(`id`,`name`,`address`) values (1,'A1','A3');
insert  into `employee`(`id`,`name`,`address`) values (1,'A2','A3');
insert  into `employee`(`id`,`name`,`address`) values (1,'A3','A4');
insert  into `employee`(`id`,`name`,`address`) values (2,'B1','B2');
insert  into `employee`(`id`,`name`,`address`) values (2,'B1','B1');
insert  into `employee`(`id`,`name`,`address`) values (3,'D','D');

You can use following query to find the duplicate id:

select id, count(id) as cnt from employee group by id order by cnt desc;

Thanks Deepak Kumar