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.
In a composite key, the whole set of elements must be unique but each element can be repeated several times.
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.
SELECT
transactionDate, purchaseOrderId, COUNT(*) CNT
FROM
purchases
GROUP BY
transactionDate, purchaseOrderId
HAVING
CNT > 1
ORDER BY
CNT ASC;
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