How to delete duplicate rows in mysql with condition?

Question

I have a table like this:

Id  email     Active
---------------------
1    aaa        1      
2    aaa        1      
3    aaa        0      
4    aaa        0          

I want to delete duplicate row but if Active have 1/0 value keep 1 value and delete 0 value.

I tried this query

select *  FROM tbl_name WHERE Id NOT IN (SELECT Id FROM tbl_name GROUP BY email)

And I expected this result :

Id  email  Active
---------------------
1    aaa        1      

OR

Id  email  Active
---------------------
2    aaa        1      

but actually result was :

Id  email  Active
---------------------    
4    aaa        0 

Thanks in advance.

Answer 1

DELETE t1
FROM test t1
JOIN test t2 ON (t1.active < t2.active OR (t1.active = t2.active AND t1.id > t2.id)) AND (t1.email = t2.email);