how to delete duplicates in mysql using case

Question

Right now I am using something like this to delete duplicates in mysql table :

delete t2 from my_table1 as t1, my_table1 as t2 where
t1.TestCase = t2.TestCase and t2.id > t1.id;

say I have a structure like this :

ID  TestCAse Result  
1   T1       PASS
2   T2       FAIL
3   T3       FAIL
4   T3       PASS

now, in the above case T3 is duplicate entry, and if I use the SQL that I mentioned above, it would delete 4th row where the result is PASS, but this is the row that I want to keep and I want row 3 to get deleted which is FAIL.

Any help please?

Thank you.

Answer 1

if I undersstand correctly in case of duplicate you want to delete the "FAIL" and not the "PASS" ? in this case you can have the following query:

delete t2 from my_table1 as t1, my_table1 as t2 where
t1.TestCase = t2.TestCase and t2.id != t1.id and t2.Result='FAIL'; 

but what do you want to do when all the duplicate have "FAIL" in their column result? With the query above, both will be removed. Do you want to keep one in this case ?