CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Grasshopper
Grasshopper

Reputation: 4987

How to Select Every Row Where Column Value is NOT Distinct

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

Upvotes: 240

Views: 431006

Answers (7)

mirazimi
mirazimi

Reputation: 878

SELECT        Title, Id
FROM            dbo.TblNews
WHERE        (Title IN
      (SELECT  Title 
FROM dbo.TblNews AS TblNews_1
GROUP BY Title
HAVING (COUNT(*) > 1)))
ORDER BY Title
  • sort in title

Upvotes: 0

Naveen Kishan
Naveen Kishan

Reputation: 41

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 <> 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.

Upvotes: 4

Serj Sagan
Serj Sagan

Reputation: 30267

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

Upvotes: 373

Nisar
Nisar

Reputation: 6038

select CustomerName,count(1) from Customers group by CustomerName having count(1) > 1

Upvotes: 27

Seasoned
Seasoned

Reputation: 1059

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)

Upvotes: 78

Chad
Chad

Reputation: 7517

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

Upvotes: 12

Marc
Marc

Reputation: 16522

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)

Upvotes: 13

Related Questions