Reputation: 416
I have TABLE1 with 3 columns(ssn, first name, last name) in it. I got list of duplicate SSNs in the table by using this query:
SELECT SSN, COUNT(SSN) FROM TABLE1 GROUP BY SSN HAVING COUNT(SSN) > 1
Now I want to get all records in the table for duplicate SSNs that I got from query above. How to do that? I am using DB2. Thanks in advance.
P.S.: I know that SSN should be unique but strangely my table has duplicate ssn's with different names.
Upvotes: 0
Views: 82
Reputation: 17462
This works by assigning a rownumber for each duplicate and selecting only that got more a rownumber lager than one.
SELECT *
FROM TABLE1
WHERE SSN IN (
select ssn from (
select ssn, row_number() over (partition by ssn) as rang from table1
) tmp
where rang>1
)
Upvotes: 0
Reputation: 25112
You can just use it in an IN
clause.
SELECT *
FROM TABLE1
WHERE SSN IN (SELECT SSN FROM TABLE1 GROUP BY SSN HAVING COUNT(SSN) > 1)
Upvotes: 1
Reputation: 133360
If can use a where in with the subselect
select * from TABLE1
where ssn in (
SELECT SSN FROM TABLE1 GROUP BY SSN HAVING COUNT(*) > 1
)
Upvotes: 0
Reputation: 1269693
Use window functions:
select t.*
from (select t.*, count(*) over (partition by ssn) as cnt
from table1
) t
where cnt > 1;
Upvotes: 1