## The Question :

*222 people think this question is useful*

In a [member] table, some rows have the same value for the `email`

column.

login_id | email
---------|---------------------
john | john123@hotmail.com
peter | peter456@gmail.com
johnny | john123@hotmail.com
...

Some people used a different login_id but the same email address, no unique constraint was set on this column. Now I need to find these rows and see if they should be removed.

What SQL statement should I use to find these rows? (MySQL 5)

*The Question Comments :*

## The Answer 1

*360 people think this answer is useful*

This query will give you a list of email addresses and how many times they’re used, with the most used addresses first.

SELECT email,
count(*) AS c
FROM TABLE
GROUP BY email
HAVING c > 1
ORDER BY c DESC

If you want the full rows:

select * from table where email in (
select email from table
group by email having count(*) > 1
)

## The Answer 2

*59 people think this answer is useful*

select email from mytable group by email having count(*) >1

## The Answer 3

*14 people think this answer is useful*

Here is query to find `email`

‘s which are used for more then one `login_id`

:

SELECT email
FROM table
GROUP BY email
HAVING count(*) > 1

You’ll need second (of nested) query to get list of `login_id`

by `email`

.

## The Answer 4

*10 people think this answer is useful*

First part of accepted answer does not work for MSSQL.

This worked for me:

select email, COUNT(*) as C from table
group by email having COUNT(*) >1 order by C desc

## The Answer 5

*5 people think this answer is useful*

use this if your email column contains empty values

select * from table where email in (
select email from table group by email having count(*) > 1 and email != ''
)

## The Answer 6

*3 people think this answer is useful*

I know this is a very old question but this is more for someone else who might have the same problem and I think this is more accurate to what was wanted.

SELECT * FROM member WHERE email = (Select email From member Where login_id = john123@hotmail.com)

This will return all records that have john123@hotmail.com as a login_id value.

## The Answer 7

*2 people think this answer is useful*

Thanks guys ðŸ™‚ I used the below because I only cared about those two columns and not so much about the rest. Worked great

select email, login_id from table
group by email, login_id
having COUNT(email) > 1

## The Answer 8

*1 people think this answer is useful*

Get the entire record as you want using the condition with inner select query.

SELECT *
FROM member
WHERE email IN (SELECT email
FROM member
WHERE login_id = abcd.user@hotmail.com)

## The Answer 9

*0 people think this answer is useful*

This works best

Screenshot

SELECT RollId, count(*) AS c
FROM `tblstudents`
GROUP BY RollId
HAVING c > 1
ORDER BY c DESC