Explore Courses Blog Tutorials Interview Questions
0 votes
in SQL by (20.3k points)

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            [email protected]

Christy          [email protected]

Jason            [email protected]

Eric             [email protected]

John             [email protected]

I need the query to return:

Aaron            [email protected]

Christy          [email protected]

John             [email protected]

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)) > 1

1 Answer

0 votes
by (40.7k points)

The below query is faster than the EXISTS way:

SELECT [EmailAddress], [CustomerName] FROM [Customers] WHERE [EmailAddress] IN

  (SELECT [EmailAddress] FROM [Customers] GROUP BY [EmailAddress] HAVING COUNT(*) > 1)

Related questions

Browse Categories