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?

SELECT email, 
 COUNT(email) AS NumOccurrences
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )
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

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

Perfect! you are the best!

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.