MySQL remove all but one value based on primary key and a column

Question

My Table

 id   userid    emailid
 ---+---------+------------
 1  |  1      |  [email protected]
 2  |  1      |  [email protected]
 3  |  123    |  [email protected]
 4  |  123    |  [email protected]
 5  |  123    |  [email protected]
 6  |  123    |  [email protected]

Result Desired

id    userid    emailid
 ---+---------+------------
 1  |  1      |  [email protected]
 2  |  1      |  [email protected]
 3  |  123    |  [email protected]
 4  |  123    |  [email protected]
 5  |  123    |  [email protected]

But I am getting

id    userid    emailid
 ---+---------+------------
 1  |  1      |  [email protected]
 2  |  1      |  [email protected]
 6  |  123    |  [email protected]

I tried (I am new to SQL)

DELETE FROM table 
WHERE id NOT IN (SELECT *
FROM (SELECT MIN(n.id) 
FROM table n GROUP BY n.emailid) x)

possible duplicate of [Delete all Duplicate Rows except for One in MySQL?](http://stackoverflow.com/questions/4685173/delete-all-duplicate-rows-except-for-one-in-mysql) — M Khalid Junaid, Aug 31 '14 at 10:50
this is not a duplicate I struggled for 3 hours to writing test queries and searching stackoverflow. The link you refer to has the wrong answer I found that unless I also included AND n1.id <> n2.id, it deleted every row in the table. I dont understand how it got 218 votes! Most other answers required me to change the way the table is defined or did not show how to delete. I closest answer that came to my question is what I have pasted above under "I tried..." until Ullas responded below. — Eve, Aug 31 '14 at 15:01

Ullas · Accepted Answer · 2014-09-18T05:37:12.313

1

Try This.

QUERY

DELETE FROM table_name 
WHERE id NOT IN
(
   SELECT MIN(id)
   FROM table_name
   GROUP BY userid,emailid
);

edited Sep 18 '14 at 05:37

answered Aug 31 '14 at 05:17

Ullas

11,450
4
33
50

MySQL remove all but one value based on primary key and a column

1 Answers1

QUERY