Duplicates

[mattlatos]

I have a table with many email addresses in it, along with other data. I want to only have unique emails in the table, and if there are more than 2 the same I want to look at another column and if the one of the other columns value is 1 then keep that one:

FIELD 1 | EMAIL
1 | [e-mail]
0 | [e-mail]

in the above example it would keep the first line. If they are both 0 then it would keep either(but only one) and the same if they are both 1's.

Is there an easy way of doing this or do I need to write a complicated algorithm to test each email against the others??

Thanks

[Sxooter]

You want something like:

select max(id), email from table where email in (select distinct email from table);

[Lars_Berg]

select max(field1),email from t group by email

[mattlatos]

I've been trying the following:

select max(field1),email from t group by email

or my code is infact:

$sql = "SELECT MAX(CONTACT_ALLOW), EMAIL, ID FROM table GROUP BY EMAIL";

$result = mysql_query($sql) or die("Error...");
while($row = mysql_fetch_array($result))
{
$ID = $row['ID'];
$email = $row['EMAIL'];
echo("$email , $ID<BR>");
}

This code returns the unique email addresses like it should do (CONTACT_ALLOW is either 1 or 0), but some of them are not the maximums from a column:

ID | CONTACT_ALLOW | EMAIL
1 | 0 |[e-mail]
2 | 1 |[e-mail]
3 | 1 |[e-mail]
4 | 1 |[e-mail]
5 | 0 |[e-mail]
6 | 0 |[e-mail]

In the above example it takes the rows with the following ID's:

1,2,3 wheras it should take: 2,3,4

Any Ideas??

[Lars_Berg]

Mysql's implementation of group by is lousy. All other RDBMS would not allow your query as id is not part of the group by clause. The mysql documentation says that the value of ID is unpredictable. It is not correlated to the record that contains the max value of contact_allow.

As far as I can see this requeires a correlated subquery which is not supported by Mysql. You need to do it in several steps

http://www.mysql.com/doc/en/example-Maximum-column-group-row.html

[ultraslacker]

I agree with Lars on mysql's implementation of GROUP BY...

Would this work for you?

SELECT CONTACT_ALLOW, EMAIL, ID
FROM table
GROUP BY EMAIL
ORDER BY CONTACT_ALLOW DESC;

***Nope - just checked - that's awful!!

[mattlatos]

I've managed to do it by using a temporary table, it probably isn't the most efficient way but it seems to work perfectly, and should only ever have to deal with a maximum of 1000 records.

Thanks for the help though!🙂

[MrAlaska]

I'm glad you got it working and temp tables are a good work around in many instances from what I have read. However, it seems your SQL statement in the earlier post should look more like this:

"SELECT EMAIL, ID FROM table
GROUP BY EMAIL
HAVING CONTACT_ALLOW=MAX(CONTACT_ALLOW)";

EDIT: However, now that I read your objective more carefully, that would still not return all the desired emails.
(at least, I don't think so, but maybe it would
trying to remember how to think)

Ok, it wouldn't if they both have the same contact_allow, you need to do a distinct someplace
giving up on thinking

[mattlatos]

I think that does work, it seems to optimize it a bit. I tried something along those lines yesterday after spending hours reading the manual!

Cheers