Foxy
Foxy

Reputation: 416

How to get table records for duplicate values?

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

Answers (4)

Esperento57
Esperento57

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

S3S
S3S

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

ScaisEdge
ScaisEdge

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

Gordon Linoff
Gordon Linoff

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

Related Questions