I have a large table with the following data on users.
social security number
name
address
I want to find all possible duplicates in the table where the ssn is equal but the name is not
My attempt is:
SELECT * FROM Table t1
WHERE (SELECT count(*) from Table t2 where t1.name <> t2.name) > 1
A grouping on SSN should do it
SELECT
ssn
FROM
Table t1
GROUP BY
ssn
HAVING COUNT(*) > 1
..or if you have many rows per ssn and only want to find duplicate names)
...
HAVING COUNT(DISTINCT name) > 1
Edit, oops, misunderstood
SELECT
ssn
FROM
Table t1
GROUP BY
ssn
HAVING MIN(name) <> MAX(name)
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