How to Select Every Row Where Column Value is NOT Distinct

Question

I need to run a select statement that returns all rows where the value of a column is not distinct  e g  EmailAddress    For example  if the table looks like below   CustomerName     EmailAddress Aaron            aaron gmail com Christy          aaron gmail com Jason            jason gmail com Eric             eric gmail com John             aaron gmail com   I need the query to return   Aaron            aaron gmail com Christy          aaron gmail com John             aaron gmail com   I have read many posts and tried different queries to no avail   The query that I believe should work is below   Can someone suggest an alternative or tell me what may be wrong with my query   select EmailAddress  CustomerName from Customers group by EmailAddress  CustomerName having COUNT distinct EmailAddress    gt  1

User · Answer

The thing that is incorrect with your query is that you are grouping by email and name, that forms a group of each unique set of email and name combined together and hence

aaron and [email protected]
christy and [email protected]
john and [email protected]

are treated as 3 different groups rather all belonging to 1 single group.

Please use the query as given below :

select emailaddress,customername from customers where emailaddress in
(select emailaddress from customers group by emailaddress having count(*) > 1)

User · Answer

This is significantly faster than the EXISTS way   SELECT  EmailAddress    CustomerName  FROM  Customers  WHERE  EmailAddress  IN    SELECT  EmailAddress  FROM  Customers  GROUP BY  EmailAddress  HAVING COUNT     gt  1

User · Answer

Rather than using sub queries in where condition which will increase the query time where records are huge   I would suggest to use Inner Join as a better option to this problem   Considering the same table this could give the result  SELECT EmailAddress  CustomerName FROM Customers as a  Inner Join Customers as b on a CustomerName  lt  gt  b CustomerName and a EmailAddress   b EmailAddress   For still better results I would suggest you to use CustomerID or any unique field of your table  Duplication of CustomerName is possible

User · Answer

select CustomerName count 1  from Customers group by CustomerName having count 1   gt  1

User · Answer

How about  SELECT EmailAddress  CustomerName FROM Customers a WHERE Exists   SELECT emailAddress FROM customers c WHERE a customerName    c customerName AND a EmailAddress   c EmailAddress

User · Answer

Just for fun  here s another way    with counts as       select CustomerName  EmailAddress        count    over  partition by EmailAddress  as num     from Customers   select CustomerName  EmailAddress from counts where num  gt  1

[sql] How to Select Every Row Where Column Value is NOT Distinct

Examples related to sql

Examples related to sql-server

Examples related to sql-server-2008