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                               [email protected]
2                               [email protected]   
3                               [email protected]
4                               [email protected]
5                               [email protected]

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        [email protected]
3        [email protected]
4        [email protected]
5        [email protected]

How do I make this happen?

lc. · Accepted Answer · 2012-07-27 19:56:04Z

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, 2012 at 19:56

lc.

117k21 gold badges164 silver badges190 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

user1542036 Over a year ago

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)

lc. Over a year ago

@user1542036 That has nothing to do with this query. It is correct (check the sqlfidddle). Do a Google search on your error.

user1542036 Over a year ago

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?

neobie Over a year ago

very slow in large table.

Abhinav bhardwaj Over a year ago

Yes it is slow in a large table or complex query we should use join (inner join) instead of this

|

Timo Huovinen · Accepted Answer · 2019-02-11 07:54:43Z

19

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, 2019 at 7:54

answered Jan 28, 2014 at 14:23

Timo Huovinen

56k34 gold badges163 silver badges150 bronze badges

1 Comment

Vineesh K S Over a year ago

Use SELECT i.id, i.payer_email FROM paypal_ipn_orders i otherwise it will show ambiguous error

Gaurav Gupta · Accepted Answer · 2015-12-17 10:38:33Z

8

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, 2015 at 10:38

Gaurav Gupta

5287 silver badges10 bronze badges

1 Comment

Marco Over a year ago

this is brilliant!

Madushan · Accepted Answer · 2014-03-03 09:58:45Z

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 3, 2014 at 9:58

Madushan

7,4781 gold badge43 silver badges84 bronze badges

answered Mar 3, 2014 at 9:36

kasiviswanatham

312 bronze badges

3 Comments

Incognito Over a year ago

By far the fastest, but you need to remove the "=" from count(*) >= 1 because it will take the ids that exists only once too.

user3533413 Over a year ago

Stack Overflow needs to allow <6 character edits. >= is simply wrong here.

ToolmakerSteve Over a year ago

@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!

Mujammil H Kazi · Accepted Answer · 2014-09-09 12:21:31Z

1

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

edited Sep 9, 2014 at 12:21

answered Mar 28, 2014 at 10:05

Mujammil H Kazi

17.4k2 gold badges37 silver badges47 bronze badges

1 Comment

ToolmakerSteve Over a year ago

Minor nit: Likely faster to replace WHERE with AND so the filtering is done during the INNER JOIN, rather than afterwards.

Incognito · Accepted Answer · 2014-09-11 17:22:58Z

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, 2014 at 17:22

Incognito

4551 gold badge7 silver badges25 bronze badges

2 Comments

Abhinav bhardwaj Over a year ago

This will not give the correct result as you are excluding the duplicate record change NOT IN to IN

ToolmakerSteve Over a year ago

@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.

M.Ganji · Accepted Answer · 2016-04-27 11:46:37Z

1

use this code

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

answered Apr 27, 2016 at 11:46

M.Ganji

87610 silver badges13 bronze badges

1 Comment

ToolmakerSteve Over a year ago

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.)

Kermit · Accepted Answer · 2012-07-27 19:58:09Z

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, 2012 at 19:58

Kermit

34.1k13 gold badges89 silver badges121 bronze badges

1 Comment

user1542036 Over a year ago

this also returns the samething as the others

Community · Accepted Answer · 2017-05-23 12:18:11Z

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, 2017 at 12:18

CommunityBot

11 silver badge

answered Jan 11, 2016 at 17:42

jdenoc

851 gold badge3 silver badges5 bronze badges

Comments

Pang · Accepted Answer · 2018-12-18 03:51:45Z

0

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

edited Dec 18, 2018 at 3:51

Pang

10.2k146 gold badges87 silver badges126 bronze badges

answered Apr 27, 2016 at 14:49

user3237205

1

1 Comment

Mario Tacke Over a year ago

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).

Filippos · Accepted Answer · 2015-06-07 21:04:13Z

-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 7, 2015 at 21:04

Filippos

1341 silver badge10 bronze badges

1 Comment

ToolmakerSteve Over a year ago

MySQL response: Not a valid query: "Invalid use of a group function". It makes no sense to use COUNT like this, without GROUP BY.

Mifeet · Accepted Answer · 2015-08-27 19:16:16Z

-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, 2015 at 19:16

Mifeet

13.8k6 gold badges62 silver badges110 bronze badges

answered Jul 27, 2012 at 19:53

ryudice

37.6k33 gold badges122 silver badges164 bronze badges

Collectives™ on Stack Overflow

Select and display only duplicate records in MySQL

12 Answers 12

7 Comments

1 Comment

1 Comment

3 Comments

1 Comment

2 Comments

1 Comment

1 Comment

Comments

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

12 Answers 12

7 Comments

1 Comment

1 Comment

3 Comments

1 Comment

2 Comments

1 Comment

1 Comment

Comments

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related