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

User · Accepted Answer

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   gt  1     sqlfiddle

User · Answer

here is the simple example     select  lt duplicate column name gt  from  lt table name gt  group by  lt duplicate column name gt  having count    gt  2   It will definitly work

User · Answer

Try this query    SELECT id  COUNT  payer email    tot  FROM paypal ipn orders GROUP BY id HAVING  tot   gt 1   Does it help

User · Answer

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   gt  1    SELECT id  payer email FROM paypal ipn orders AS p INNER JOIN duplicates AS d ON d payer email p payer email

User · Answer

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

User · Answer

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    gt  1   j ON i payer email j payer email

User · Answer

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   gt 1     SORT BY id

User · Answer

use  this code      SELECT         FROM  paypal ipn orders      GROUP BY  payer email       HAVING COUNT  payer email   gt 1

User · Answer

SELECT   FROM  table  t1 join  table  t2 WHERE  t1 name t2 name   amp  amp   t1 id  t2 id

User · Answer

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

User · Answer

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     gt  1

User · Answer

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     gt   1

[mysql] Select and display only duplicate records in MySQL

Examples related to mysql

Examples related to duplicates