Finding Duplicate Rows in MySQL (Composite Key)

Question

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.

Tumultuous

The query looks correct to me - it might be worth checking your data again? – Maness

@FionaT - I looked again and I think you're actually correct. I wasn't expecting a million duplicate rows, but the random rows that I've checked are being duplicated (the table is quite large - 20+ million rows). So maybe my original query was correct. – Tumultuous

Answer 1

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

Neddie

Answer 2

Alter your transactionDate column to 'date' datatype and insert the values in proper date format i.e. ('YYYY-MM-DD'). Insert the values as:

    INSERT into purchases values ('2012-08-09', 55);

Then use your own above written query. It will give you the result as you want.

Welcher

The column is already in Date format and does have a proper date format. As I noted in my comment response to my own question, the query in my question is already giving correct responses, but I did not expect that there would be so many duplicate rows. – Tumultuous