How do I know if there are multiple rows with the same value in a particular column?

Question

I am looking for an effective way to exclude rows from a SELECT WHERE SELECT more than one row is returned with the same value for a particular column.

In particular, I select a bunch of accounts, but you need to exclude accounts where more than one is found with the same name as the SSN.

Answer 1

Thank you all for the detailed suggestions. When everything was said and done, I needed to use a correlated subquery . Essentially, this is what I had to do:

 SELECT acn, ssn, [date] FROM Account a WHERE NOT EXISTS (SELECT 1 FROM Account WHERE ssn = a.ssn AND [date] < a.[date]) 

Hope this helps someone.

---

I never updated this ... In my final submission, I achieved this through the left join to increase efficiency (the correlated subquery was not acceptable, as it took a considerable amount of time to run, checking each record for more than 150 thousand others).

Here is what should have been done to solve my problem:

 SELECT acn, ssn FROM Account a LEFT JOIN (SELECT ssn, COUNT(1) AS counter FROM Account GROUP BY ssn) AS counters ON a.ssn = counters.ssn WHERE counter IS NULL OR counter = 0 

Answer 2

this will return all SSNs with exactly 1 row

 select ssn,count(*) from SomeTable group by ssn having count(*) = 1 

this will return all SSNs with more than 1 row

 select ssn,count(*) from SomeTable group by ssn having count(*) > 1 

Your complete query will be like this (will work on SQL Server 7 and above)

 select a.* from account a join( select ssn from SomeTable group by ssn having count(*) = 1) s on a.ssn = s.ssn 

Answer 3

For SQL 2005 or higher, you can try the following:

 WITH qry AS ( SELECT a.*, COUNT(*) OVER(PARTITION BY ssn) dup_count FROM accounts a ) SELECT * FROM qry WHERE dup_count = 1 

For SQL 2000 and 7:

 SELECT a.* FROM accounts a INNER JOIN ( SELECT ssn FROM accounts b GROUP BY ssn HAVING COUNT(1) = 1 ) b ON a.ssn = b.ssn 

Answer 4

 SELECT * FROM #Temp WHERE SSN NOT IN (SELECT ssn FROM #Temp GROUP BY ssn HAVING COUNT(ssn) > 1)