0 votes
1 view
in SQL by (20.3k points)

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

login_id | email

---------|---------------------

john     | [email protected]

peter    | [email protected]

johnny   | [email protected]

...

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)

1 Answer

0 votes
by (40.3k points)

For full rows, you can use this query:

select * from table where email in (

    select email from table

    group by email having count(*) > 1

)

If you want a list of email addresses and how many times they're used, with the most used addresses first. Then use the query given below:

SELECT email,

       count(*) AS c

FROM TABLE

GROUP BY email

HAVING c > 1

ORDER BY c DESC

Related questions

+2 votes
1 answer
0 votes
1 answer
asked Jul 20, 2019 in SQL by Tech4ever (20.3k points)
0 votes
1 answer
0 votes
1 answer
asked Jul 31, 2019 in SQL by Tech4ever (20.3k points)
Welcome to Intellipaat Community. Get your technical queries answered by top developers !


Categories

...