Find duplicate data in table using mysql

Question

This my table structure :

id    emp_id    log_date
1        1      2015-1-1
2        1      2015-1-1
3        1      2015-1-2
4        2      2015-1-1
5        2      2015-1-2
6        2      2015-1-2

I want to pull duplicate log_date for every emp_id seperately.

Expected output:

id    emp_id    log_date
1        1      2015-1-1
2        1      2015-1-1
5        2      2015-1-2
6        2      2015-1-2

Possible duplicate of [Find duplicate records in MySQL](http://stackoverflow.com/questions/854128/find-duplicate-records-in-mysql) — Tom Lord, Jun 27 '16 at 11:25

score 1 · Accepted Answer · answered Jun 27 '16 at 10:25

you can find duplicate records by many ways. Some of are as below: 1.

SELECT * FROM `table_name` GROUP BY emp_id, log_date having count(emp_id) > 1;

By the below query you can fetch the exact times the record is repeating.

SELECT emp_id, count(*) as cnt FROM `table_name` GROUP BY log_date having cnt > 1 ORDER BY cnt desc=;

Second way is to use the auto increment Id that is 'id'. It is always unique so we can use to it to find duplicate records.

SELECT n1.* FROM table_name n1, table_name n2 WHERE n1.`id` < n2.`id` AND n1.emp_id = n2.emp_id AND n1.log_date = n2.log_date

Hope this will work. Please Mark the anset as accepted if you are satisfied. Thanks! :)

vivek kumar singh · Answer 2 · 2016-06-27T10:34:32.920

1

You can use below mentioned query for required result :

select id,emp_id,log_data from table group by emp_id,log_data;

If you want only duplicate data then use:

select id,emp_id,log_data from table group by emp_id,log_data having count(1)>1;

edited Jun 27 '16 at 10:34

answered Jun 27 '16 at 10:26

vivek kumar singh

133
1
9

score 0 · Answer 3 · answered Jun 27 '16 at 10:21

0

Try this;)

select t1.*
from yourtable t1
inner join (
    select emp_id, log_date from yourtable group by emp_id, log_date having count(*) > 1
) t2 on t2.emp_id = t1.emp_id and t2.log_date = t1.log_date

SQLFiddle DEMO HERE

answered Jun 27 '16 at 10:21

Blank

12,308
1
14
32

There could be more optimized way, Please try this : SELECT n1.* FROM table_name n1, table_name n2 WHERE n1.`id` < n2.`id` AND n1.emp_id = n2.emp_id AND n1.log_date = n2.log_date – Aman Garg Jun 27 '16 at 11:56
You can refer to the link below, it have many and very optimized solutions: http://stackoverflow.com/questions/38051373/find-duplicate-data-in-table-using-mysql – Aman Garg Jun 30 '16 at 11:42
@AmanGarg Wow, what do you want me to do? Accept your answer? – Blank Jul 01 '16 at 01:39

score 0 · Answer 4 · answered Jun 27 '16 at 10:21

0

You can use cross join as follows. The query is simply checking if the data matches but the id doesn't.

SELECT t1.* 
FROM
tablename t1, tablename t2
WHERE t1.id <> t2.id 
AND t1.emp_id = t2.emp_id
AND t1.log_date = t2.log_date

answered Jun 27 '16 at 10:21

Sujeet Sinha

2,417
2
18
27

score 0 · Answer 5 · answered Jun 27 '16 at 10:30

0

you can also do it with HAVING like this:

SELECT COUNT(*) AS cnt, t.*
FROM yourtable t
group by emp_id,log_date
having cnt >1;

answered Jun 27 '16 at 10:30

Bernd Buffen

14,525
2
24
39

Find duplicate data in table using mysql

5 Answers5