MySQL Find rows that have the same value on a column

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.

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
)

Comments

Post a Comment

Popular posts from this blog

SwiftUI - TEXT

TEXT Text is used to display one or more lines of text content with the same effect as UILabel, but it is even better. If you want to create Text, just create it with Text("SwiftUI"); With chained syntax, you can also add multiple attributes to the text, such as fonts, colors, shadows, spacing between top left and right, and so on. Example: Text ( "SwiftUI" ) . foregroundColor ( . orange ) . bold () . font ( . system ( . largeTitle )) . fontWeight ( . medium ) . italic () . shadow ( color : . black , radius : 1 , x : 0 , y : 2 )
Read more

SwiftUI - TEXTFIELD

TEXTFIELD TextField is used to add a normal input box, which is often used as a text input. Example： TextField ( self . $ name , placeholder : self . nameText , onEditingChanged : { changed in print ( "onEditing: \( changed ) " ) }) { print ( "userName: \( self . name ) " ) self . endEditing ( true ) }} . padding ( 10 ) . frame ( height : 50 ) . textFieldStyle ( RoundedBorderTextFieldStyle ()) . padding ( EdgeInsets ( top : 0 , leading : 20 , bottom : 0 , trailing : 20 ))
Read more