how to delete duplicates in mysql using case
- by JPro
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.
