How do I see if there are multiple rows with an identical value in particular column?

Question

I'm looking for an efficient way to exclude rows from my SELECT statement WHERE more than one row is returned with an identical value for a certain column.

Specifically, I am selecting a bunch of accounts, but need to exclude accounts where more than one is found with the same SSN associated.

Answer 1

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 full query would be like this (will work on SQL Server 7 and up)

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

Answer 2

For SQL 2005 or above you can try this:

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