Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Finding Duplicate Rows in MySQL (Composite Key)

I have a table with rows in the following format:

transactionDate, purchaseOrderId
8/8/2012, 55
8/9/2012, 55
8/8/2012, 88
8/9/2012, 55
8/10/2012, 77

I want to find all rows where the transactionDate and purchaseOrderId are both exact. So the following two rows are duplicates:

8/9/2012, 55
8/9/2012, 55

I tried using the following query:

SELECT
    transactionDate, purchaseOrderId
FROM
    purchases
GROUP BY
    transactionDate, purchaseOrderId
HAVING COUNT(*) > 1;

However it returned the Aug 8th result in addition to the Aug 9th result. How do I make it only return fields where both columns are duplicated (instead of returning all transactions for each date)?

Thanks.

like image 553
David Avatar asked Aug 24 '12 18:08

David


People also ask

Can composite keys have duplicates?

In a composite key, the whole set of elements must be unique but each element can be repeated several times.

How do you find duplicate values in a primary key field?

Using the GROUP BY clause to group all rows by the target column(s) – i.e. the column(s) you want to check for duplicate values on. Using the COUNT function in the HAVING clause to check if any of the groups have more than 1 entry; those would be the duplicate values.


1 Answers

SELECT 
    transactionDate, purchaseOrderId, COUNT(*) CNT 
FROM 
    purchases 
GROUP BY 
    transactionDate, purchaseOrderId 
HAVING 
    CNT > 1
ORDER BY 
    CNT ASC;
like image 178
Ryan Kempt Avatar answered Oct 11 '22 01:10

Ryan Kempt