CODEWITHSUNDEEP
mysqlduplicates
user1542036
user1542036

Reputation: 453

Select and display only duplicate records in MySQL

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?

Upvotes: 41

Views: 120956

Answers (12)

Timo Huovinen
Timo Huovinen

Reputation: 55673

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

Upvotes: 19

user3237205
user3237205

Reputation: 1

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

Upvotes: 0

M.Ganji
M.Ganji

Reputation: 866

use this code 

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

Upvotes: 1

jdenoc
jdenoc

Reputation: 85

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;

Upvotes: 0

Gaurav Gupta
Gaurav Gupta

Reputation: 518

here is the simple example : 

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

It will definitly work. :)

Upvotes: 8

ryudice
ryudice

Reputation: 37456

Try this query: 

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

Does it help?

Upvotes: -2

Filippos
Filippos

Reputation: 134

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;

Upvotes: -1

Incognito
Incognito

Reputation: 455

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

Upvotes: 1

Sameer Kazi
Sameer Kazi

Reputation: 17359

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

Upvotes: 1

kasiviswanatham
kasiviswanatham

Reputation: 31

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

Upvotes: 2

Kermit
Kermit

Reputation: 34062

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)

Upvotes: 0

lc.
lc.

Reputation: 116528

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

Upvotes: 78

Related Questions