Question 20904 in Mysql Question posted in
The official MySQL documentation can be found here.

Eliminate the duplicate rows from the table in SQL – Mysql

I want to eliminate the duplicate rows based on email from the table and retrieve all the rows without duplicates.

I have tried using distinct but I’m not getting desired results.

SELECT 
  DISTINCT Email 
FROM 
  Users

Example Table:

Id Email Username
1 [email protected] sam1122
2 [email protected] john1122
3 [email protected] sam2233
4 [email protected] lily@as

What I want to retrieve:

Id Email Username
1 [email protected] john1122
2 [email protected] lily@as
Tags:

5

Answers


  1. 0 
    SELECT Id, Email, Username
FROM Users
WHERE Email IN (
    SELECT Email
    FROM Users
    GROUP BY Email
    HAVING COUNT(*) = 1
)
    Login or Signup to reply.
  2. 0

    We can try using exists logic here:

    SELECT Id, Email, Username
FROM Users u1
WHERE NOT EXISTS (
    SELECT 1
    FROM Users u2
    WHERE u2.Email = u1.Email AND
          u2.Id <> u1.Id
);
    Login or Signup to reply.
  3. 0 
    SELECT id,
   Email,
   Username,
   count(*) AS duplicate_email_count
FROM Users
GROUP BY Email
HAVING duplicate_email_count=1
    Login or Signup to reply.
  4. 0

    You can do it using left join :

    select u.*
from Users u
left join (
  select email, max(id) as Id
  from Users
  group by email
  having count(1) > 1
) as s on s.email = u.email
where s.email is null;

    Demo here

    Login or Signup to reply.
  5. 0

    Yet another option, if you are using MySQL 8 –

    SELECT Id, Email, Username
FROM (
    SELECT *, COUNT(*) OVER (PARTITION BY Email) AS cnt
    FROM Users
) t
WHERE t.cnt = 1;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search