Can anyone help me with "Delete duplicate emails" problem in database mysql ?


  • 0
    L

    The problem is:
    Write a SQL query to delete all duplicate email entries in a table named Person, keeping only unique emails based on its smallest Id.

    +----+------------------+
    | Id | Email            |
    +----+------------------+
    | 1  | john@example.com |
    | 2  | bob@example.com  |
    | 3  | john@example.com |
    +----+------------------+
    
    Id is the primary key column for this table.
    For example, after running your query, the above Person table should have the following rows:
    
    +----+------------------+
    | Id | Email            |
    +----+------------------+
    | 1  | john@example.com |
    | 2  | bob@example.com  |
    +----+------------------+
    

    My solution is as below. It's not accepted, but I can't figure out why. Can anyone kindly point out to me what's wrong with my solution?

    select  a.Id, a.Email
    from Person as a , (select Id from Person group by Email having Id=min(Id) ) as b
    where a.Id=b.Id
    

    THANK YOU!


  • 0

    I have my sql query as this, and it passed:

    delete from Person where Id not in (select Id from (select min(Id) as Id from Person group by Email) a);

  • 0
    W

    delete from Person where Id in (select Id from (select Id, ROW_NUMBER() OVER (PArtition By Email) as row1 from Person order by Email) as abc where row1 > 1)


  • 0
    C

    My solution which not calls buit-in functions beats 99.08%. Hope this will help you!

    DELETE FROM Person 
    WHERE id IN (
      SELECT id FROM (
        SELECT a.id 
        FROM Person a INNER JOIN Person b USING(email) 
        WHERE a.id>b.id
      ) c
    )

Log in to reply
 

Looks like your connection to LeetCode Discuss was lost, please wait while we try to reconnect.