Select and display only duplicate records in MySQL

Question

This question is pretty simple I for some reason can't get the proper result to display only the duplicate records

Table   : Paypal_ipn_orders
id                              payer_email
1                               susan@gmail.com
2                               ryan@gmail.com   
3                               susan@gmail.com
4                               steve@gmail.com
5                               steve@gmail.com

SELECT id, COUNT( payer_email ) `tot`
FROM paypal_ipn_orders
GROUP BY payer_email
HAVING `tot` >1

sample output

id       tot
1         2
4         2

expected output

id       payer_email 
1        susan@gmail.com
3        susan@gmail.com
4        steve@gmail.com
5        steve@gmail.com

How do I make this happen?

score 78 · Accepted Answer · answered Jul 27 '12 at 19:56

78

SELECT id, payer_email
FROM paypal_ipn_orders
WHERE payer_email IN (
    SELECT payer_email
    FROM paypal_ipn_orders
    GROUP BY payer_email
    HAVING COUNT(id) > 1
)

sqlfiddle

answered Jul 27 '12 at 19:56

lc.

113,939
20
158
187

that returns this `unknown table status: TABLE_TYPE unknown table status: TABLE_TYPE` and `Showing rows 0 - -1 ( 0 total, Query took 39.6457 sec)` – user1542036 Jul 30 '12 at 03:32
@user1542036 That has nothing to do with this query. It is correct (check the sqlfidddle). Do a Google search on your error. – lc. Jul 30 '12 at 03:41
the only thing I find is it appears to be a problem with the particular version of phpmyadmin? I'm running 3.4.1 so upgrading to the newest version should fix it? – user1542036 Jul 30 '12 at 04:09
4

very slow in large table. – neobie Aug 15 '16 at 16:45
Thanks mate, you just saved me lots of time. – Charas Feb 15 '17 at 04:39
2

Yes it is slow in a large table or complex query we should use join (inner join) instead of this – Abhinav bhardwaj Aug 08 '18 at 09:54
See [Timo's answer using Join](https://stackoverflow.com/a/21408164/199364) for better performance. – ToolmakerSteve Jun 16 '21 at 17:37

Timo Huovinen · Answer 2 · 2019-02-11T07:54:43.263

17

The IN was too slow in my situation (180 secs)

So I used a JOIN instead (0.3 secs)

SELECT i.id, i.payer_email
FROM paypal_ipn_orders i
INNER JOIN (
 SELECT payer_email
    FROM paypal_ipn_orders 
    GROUP BY payer_email
    HAVING COUNT( id ) > 1
) j ON i.payer_email=j.payer_email

edited Feb 11 '19 at 07:54

answered Jan 28 '14 at 14:23

Timo Huovinen

53,325
33
152
143

Use SELECT i.id, i.payer_email FROM paypal_ipn_orders i otherwise it will show ambiguous error – V A S May 27 '16 at 12:05

score 7 · Answer 3 · answered Dec 17 '15 at 10:38

7

here is the simple example :

select <duplicate_column_name> from <table_name> group by <duplicate_column_name> having count(*)>=2

It will definitly work. :)

answered Dec 17 '15 at 10:38

Gaurav Gupta

478
6
10

this is brilliant! – Marco Jan 19 '21 at 20:04

score 2 · Answer 4 · edited Mar 03 '14 at 09:58

2

Get a list of all duplicate rows from table:

Select * from TABLE1 where PRIMARY_KEY_COLUMN NOT IN ( SELECT PRIMARY_KEY_COLUMN
FROM TABLE1 
GROUP BY DUP_COLUMN_NAME having (count(*) >= 1))

edited Mar 03 '14 at 09:58

Madushan

6,977
31
79

answered Mar 03 '14 at 09:36

kasiviswanatham

31
2

1

By far the fastest, but you need to remove the "=" from count(*) >= 1 because it will take the ids that exists only once too. – Incognito Sep 11 '14 at 17:16
Stack Overflow needs to allow <6 character edits. >= is simply wrong here. – Spencer Stewart May 21 '21 at 12:59
@Incognito - NO, the purpose of `>=` is to INCLUDE non-duplicate rows; then `NOT IN` returns the "extra" rows when there are duplicates. Note that this is slighly different than OP's request, which would list BOTH rows of each duplicate. If you do `>`, then `NOT IN` will give you many non-duplicate rows. In that case, if used the resulting IDs to delete duplicates, the result would be disastrous! – ToolmakerSteve Jun 16 '21 at 17:41

Sameer Kazi · Answer 5 · 2014-09-09T12:21:31.573

Hi above answer will not work if I want to select one or more column value which is not same or may be same for both row data

For Ex. I want to select username, birth date also. But in database is username is not duplicate but birth date will be duplicate then this solution will not work.

For this use this solution Need to take self join on same table/

SELECT  
    distinct(p1.id),  p1.payer_email , p1.username, p1.birth_date

FROM 
    paypal_ipn_orders AS p1 

INNER JOIN paypal_ipn_orders AS p2 

ON p1.payer_email=p2.payer_email

WHERE 

p1.birth_date=p2.birth_date

Above query will return all records having same email_id and same birth date

Minor nit: Likely faster to replace `WHERE` with `AND` so the filtering is done during the `INNER JOIN`, rather than afterwards. — ToolmakerSteve, Jun 16 '21 at 17:43

score 1 · Answer 6 · answered Sep 11 '14 at 17:22

1

This works the fastest for me

SELECT
    primary_key
FROM
    table_name
WHERE
    primary_key NOT IN (
        SELECT
            primary_key
        FROM
            table_name
        GROUP BY
            column_name
        HAVING
            COUNT(*) = 1
    );

answered Sep 11 '14 at 17:22

Incognito

435
1
7
23

This will not give the correct result as you are excluding the duplicate record change NOT IN to IN – Abhinav bhardwaj Aug 01 '18 at 06:47
@Abhinavbhardwaj - I've tested this answer, and it works. The `inner SELECT` finds keys of all NOT-DUPLICATED column values. Then `NOT IN` excludes those, thereby finding all DUPLICATES. – ToolmakerSteve Jun 16 '21 at 18:43

score 1 · Answer 7 · answered Apr 27 '16 at 11:46

1

use this code

 SELECT *  
    FROM  paypal_ipn_orders 
    GROUP BY  payer_email  
    HAVING COUNT( payer_email) >1

answered Apr 27 '16 at 11:46

M.Ganji

818
8
13

Not correct. will show only one row per GROUP BY value. What happens if there are THREE with same email? This will only list ONE of them. (If that is rare, could run this again, to catch any remaining duplicates. Repeat until zero rows returned.) – ToolmakerSteve Jun 16 '21 at 18:22

score 0 · Answer 8 · answered Jul 27 '12 at 19:58

0

SELECT id, payer_email FROM paypal_ipn_orders
WHERE payer_email IN (
    SELECT payer_email FROM papypal_ipn_orders GROUP BY payer_email HAVING COUNT(*) > 1)

answered Jul 27 '12 at 19:58

Kermit

33,827
13
85
121

1

this also returns the samething as the others – user1542036 Jul 30 '12 at 03:38

score 0 · Answer 9 · edited May 23 '17 at 12:18

0

Similar to this answer, though I used a temporary table instead:

CREATE TEMPORARY TABLE duplicates (
    SELECT payer_email
    FROM paypal_ipn_orders
    GROUP BY payer_email
    HAVING COUNT(id) > 1
);
SELECT id, payer_email
FROM paypal_ipn_orders AS p
INNER JOIN duplicates AS d ON d.payer_email=p.payer_email;

edited May 23 '17 at 12:18

Community

1
1

answered Jan 11 '16 at 17:42

jdenoc

75
1
3
5

score 0 · Answer 10 · edited Dec 18 '18 at 03:51

0

SELECT * FROM `table` t1 join `table` t2 WHERE (t1.name=t2.name) && (t1.id!=t2.id)

edited Dec 18 '18 at 03:51

Pang

9,564
146
81
122

answered Apr 27 '16 at 14:49

user3237205

1

3

Can you explain your solution to the problem? You may also use the code indentation feature of the editor to highlight the SQL (four spaces). – Mario Tacke Apr 27 '16 at 15:12

score -1 · Answer 11 · answered Jun 07 '15 at 21:04

-1

I think this way is the simplier. The output displays the id and the payer's email where the payer's email is in more than one record at this table. The results are sorted by id.

    SELECT id, payer_email
    FROM paypal_ipn_orders
    WHERE COUNT( payer_email )>1
    SORT BY id;

answered Jun 07 '15 at 21:04

Filippos

134
1
10

MySQL response: Not a valid query: "Invalid use of a group function". It makes no sense to use COUNT like this, without GROUP BY. – ToolmakerSteve Jun 16 '21 at 18:30

score -2 · Answer 12 · edited Aug 27 '15 at 19:16

-2

Try this query:

SELECT id, COUNT( payer_email ) `tot`
FROM paypal_ipn_orders
GROUP BY id
HAVING `tot` >1

Does it help?

edited Aug 27 '15 at 19:16

Mifeet

12,949
5
60
108

answered Jul 27 '12 at 19:53

ryudice

36,476
32
115
163

Select and display only duplicate records in MySQL

12 Answers12

Linked

Related