Member Avatar for welbyobeng

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
  • 111 Views
  • 1 Day Discussion Span
  • Latest Post Latest Post by welbyobeng
Member Avatar for peter_budo 
SELECT email, 
 COUNT(email) AS NumOccurrences
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )
Member Avatar for welbyobeng 
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

Member Avatar for peter_budo

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

Member Avatar for welbyobeng

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.