Removing Duplicates From MySQL DB

[Arc]

Hi, i have a table that has some identical and some almost identical rows.

Basically i want to remove all rows where the "EmailAddress" field is the same but i want to remove the duplicate where another field called "Active" = TRUE as apposed to the one that = FALSE.

So i have 6 Fields

EmailAddress, ClientName,EmailsSent,Active,EmailsRead,DateCanceled

2 rows would look like this

[e-mail] | PaulMiller | 3 | TRUE | 3 | NULL
[e-mail] | PaulMiller | 1 | FALSE | 0 | 2003-03-23

I want to remove the first row and keep the second one.

Also if the two rows are identical i would also want to remove one of them

[e-mail] | PaulMiller | 1 | FALSE | 0 | 2003-03-23
[e-mail] | PaulMiller | 1 | FALSE | 0 | 2003-03-23

removing either one of these is fine.

I am not sure how to go about this and i've read that it can't easily be done with MySQL, but maybe there's a work around?

Thanks!😃

[jstarkey]

You should probably do a "SELECT DISTINCT ( email ) FROM your_table" and then loop through that looking for dupes by selecting the email address in another query and using mysql_num_rows(). Once you find a dupe, compare the other fields and dump the one that you don't want.

I can't see any easy way of doing what you want to accomplish.