Mysql Command for Duplicates

[rajivadhy]

Hi,

Need help filtering duplicates from a category

fname- catid ( id of category)
fname- company_name ( name of company)

For catid () find all duplicate company_name

Some categories eg. have the same company name 5 times

What will be the sql command in phpmyadmin

Regards

[brotherhood of LAN]

Using Group By would return only unique company names.

SELECT * 
FROM table
WHERE ...
GROUP BY company_name

[rajivadhy]

sorry how would the full query be ?

[brotherhood of LAN]

If you post the query you currently have, then I'd be able to add to it as I don't know the table names or what you want to do with the query.

That said, if you try putting the GROUP BY statement after your WHERE statement, it should work fine.

[rajivadhy]

I dont have a queury
Need to
UPDATE table_product and remove duplicate company_name ( name of company)
from cat_id (category)

[brotherhood of LAN]

I'm not quite sure what you want to do, hopefully you can elaborate a bit more or someone else can chip in. Sorry I couldn't help out!

[rocknbil]

Sometimes the universe just . . . aligns. :-) I have a project where just last evening, the **exact** same issue came up. Unfortunately, I haven't found a way to do it in one statement, but I *have* figured out a way to correctly count it and use that as a trigger to execute a nested command in programming, which is at least a *little* more efficient than searching the entire table.

Start with how to determine the duplicates. Working example:

create table test2 (id int(11) primary key auto_increment, some_val varchar(255));

insert into test2 (some_val) values ('abcd');
insert into test2 (some_val) values ('abcde');
insert into test2 (some_val) values ('abcd');
insert into test2 (some_val) values ('abcde');
insert into test2 (some_val) values ('abcd');
insert into test2 (some_val) values ('abcdfg');
insert into test2 (some_val) values ('abcdhi');
insert into test2 (some_val) values ('abcdjk');

then

select count(*) as num, id,some_val from test2 group by some_val having count(*) > 1;

-->

num | id | some_val
3 | 1 | abcd
2 | 2 | abcde

Note it reports the first instance of the duplicate for the id. so using this, I know that records with id = 1 or id = 2 have duplicates, I can then execute like this:

$query = "select count(*) as num, id,some_val from test2 group by some_val having count(*) > 1";  
$result = mysql_query($query);  
while ($row = mysql_fetch_array($result)) {  
 $query = "update test2 set some_val = concat(some_val,'-',id) where some_val='" .  
 $row[2] . "' and id <> " . $row[1]; 
 mysql_query($query); 
}  

The above code is untested, make sure you don't try this on live data. But I think it will work . . . maybe someone can extract a single nested query from the groundwork here, that would sure make life easier . . . .