Back

Explore Courses Blog Tutorials Interview Questions
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.4k 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
0 votes
1 answer
asked Jul 20, 2019 in SQL by Tech4ever (20.3k points)
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!

28.4k questions

29.7k answers

500 comments

94k users

Browse Categories

...