Latest Tutorials| Questions and Answers|Ask Questions?|Site Map



Home Sql Mysql-example Mysql Find Duplicate Records

Related Tutorials


 
 

Share on Google+Share on Google+

Mysql Find Duplicate Records

Advertisement
Mysql Find Duplicate Records is used to find the duplicate Records in the table.

Mysql Find Duplicate Records

     

Mysql Find Duplicate Records is used to find the duplicate Records in the table.

Understand with Example

The Tutorial illustrate an example from 'Mysql Find Duplicate Records'. To grasp and understand example, the select query is used to return the records from table 'employee12'.

 select * from employee12;

Table structure:

+-------+----------+--------+------------+
| Empid | Empname  | Salary | DOB        |
+-------+----------+--------+------------+
|     1 | Habib    |   2014 | 2004-12-02 |
|     2 | Karan    |   4021 | 2003-04-11 |
|     3 | Samia    |     22 | 2008-02-23 |
|     4 | Hui Ling |     25 | 2008-10-15 |
|     5 | Yumie    |     29 | 1999-01-26 |
|    10 | NULL     | 210000 | 2009-01-12 |
|    10 | jjlkl    |   2222 | 2008-02-13 |
|    10 | hkjkkjl  |   2222 | 2008-02-14 |
+-------+----------+--------+------------+

Query for finding duplicate records:

The Query is used to find the maximum number of duplicate records

mysql> SELECT *, count(*) as n
    ->  FROM employee12
    ->  group by empid
    ->  HAVING n>1;

Output:-

+-------+---------+--------+------------+---+
| Empid | Empname | Salary | DOB        | n |
+-------+---------+--------+------------+---+
|    10 | NULL    | 210000 | 2009-01-12 | 3 |
+-------+---------+--------+------------+---+
1 row in set (0.00 sec)
Advertisement

If you enjoyed this post then why not add us on Google+? Add us to your Circles



Liked it!  Share this Tutorial


Follow us on Twitter, or add us on Facebook or Google Plus to keep you updated with the recent trends of Java and other open source platforms.

Posted on: January 24, 2009

Related Tutorials

Discuss: Mysql Find Duplicate Records  

Post your Comment


Your Name (*) :
Your Email :
Subject (*):
Your Comment (*):
  Reload Image
 
 
Comments:6
Neomaster
November 29, 2011
Good query

This is good query and for more detail use following example: -- -- Table structure for table `employee` -- CREATE TABLE IF NOT EXISTS `employee` ( `id` int(11) DEFAULT NULL, `first_name` varchar(15) DEFAULT NULL, `last_name` varchar(15) DEFAULT NULL, `start_date` date DEFAULT NULL, `end_date` date DEFAULT NULL, `salary` float(8,2) DEFAULT NULL, `city` varchar(10) DEFAULT NULL, `description` varchar(15) DEFAULT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `employee` -- INSERT INTO `employee` (`id`, `first_name`, `last_name`, `start_date`, `end_date`, `salary`, `city`, `description`) VALUES (1, 'Jason', 'Martin', '1996-07-25', '2006-07-25', 1234.56, 'Toronto', 'Programmer'), (2, 'Alison', 'Mathews', '1976-03-21', '1986-02-21', 6661.78, 'Vancouver', 'Tester'), (3, 'James', 'Smith', '1978-12-12', '1990-03-15', 6544.78, 'Vancouver', 'Tester'), (4, 'Celia', 'Rice', '1982-10-24', '1999-04-21', 2344.78, 'Vancouver', 'Manager'), (5, 'Robert', 'Black', '1984-01-15', '1998-08-08', 2334.78, 'Vancouver', 'Tester'), (6, 'Linda', 'Green', '1987-07-30', '1996-01-04', 4322.78, 'New York', 'Tester'), (7, 'David', 'Larry', '1990-12-31', '1998-02-12', 7897.78, 'New York', 'Manager'), (8, 'James', 'Cat', '1996-09-17', '2002-04-15', 1232.78, 'Vancouver', 'Tester'), (NULL, 'James', 'Cat', '2011-11-29', '2011-11-30', 30000.00, 'New Yark', 'no desc'), (NULL, 'David', 'Larry', '2011-11-01', '2011-11-30', 50000.00, 'New Yark', 'no desc'); -- -- Get duplicate from table `employee` -- SELECT * , count( * ) AS Iteration FROM employee GROUP BY first_name HAVING Iteration > 1 LIMIT 0 , 30
Vimal Raj
January 18, 2012
Removing Duplicate Records

This query is quite useful to find the duplicate records. But i need the query to delete those duplicate records. Can anyone help me out ??
abrightworker
January 23, 2012
Feedback

Nice example
Prashant
March 29, 2012
Need help to find duplicate record

Hi, I want to find the duplicate records from a row. I know group by clause is there, but I want to filter the records depending on some other conditions. I want to use where condition also. Please help me.
soft
July 12, 2012
Good post

My peoblem id solved thanks
Immanuel
November 16, 2012
Duplicate Records

Is there a way I can view the records without grouping them? selecting only the repeated ones?
DMCA.com