Menu
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?
600
To select duplicate values, you need to create groups of rows with the same values and then select the groups with counts greater than one. You can achieve that by using GROUP BY and a HAVING clause.
Once you have grouped data you can filter out duplicates by using having clause. Having clause is the counterpart of where clause for aggregation queries. Just remember to provide a temporary name to count() data in order to use them in having clause.
Note − Use the INSERT IGNORE command rather than the INSERT command. If a record doesn't duplicate an existing record, then MySQL inserts it as usual. If the record is a duplicate, then the IGNORE keyword tells MySQL to discard it silently without generating an error.
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
148
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 If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
