0

I am using mysql and I have a table called "users". how will i get it to show only rows that have duplicate emails in the "email" field. I want the emails in order. what sql query will I use?

2
Contributors
4
Replies
5
Views
9 Years
Discussion Span
Last Post by welbyobeng
0
SELECT email, 
 COUNT(email) AS NumOccurrences
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )

Yea I know that query already but I want it to show the duplicates so that i can leave on and delete the another. It is just showing me the emails and how much is duplicated

0

My table (ordered by email so it is easier to see duplicates)

uid, email
'marta', 'marta@marta.co.uk'
'petra', 'marta@marta.co.uk'
'michael', 'michael@micheal.co.uk'
'peter2', 'peter2@peter2.co.uk'
'marek', 'peter@peter.co.uk'
'peter', 'peter@peter.co.uk'
'peter3', 'peter@peter.co.uk'
'vicky', 'vicky@vicky.co.uk'

Query

SELECT * FROM user
WHERE EXISTS
(SELECT * FROM user AS user2
WHERE user2.uid <> user.uid
AND user2.email = user.email)
order by email;

Result

'marta', 'marta@marta.co.uk'
'petra', 'marta@marta.co.uk'
'marek', 'peter@peter.co.uk'
'peter', 'peter@peter.co.uk'
'peter3', 'peter@peter.co.uk'

Is that what you want? If not, use my table set to show desired output

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.