Hello, You can try delete/filter the redundant rows with this code: (this is an option :D)
create table yourtable ( id int not null auto_increment, city varchar(20), cc varchar(2), primary key(id) ); insert into yourtable values (0,'AAA','AA'); ... insert into yourtable values (0,'DDD','CC'); +----+------+------+ | id | city | cc | +----+------+------+ | 1 | AAA | AA | | 2 | AAA | AA | | 3 | AAA | AA | | 4 | BBB | BB | | 5 | BBB | BB | | 6 | BBB | BB | | 7 | BBB | BB | | 8 | CCC | CC | | 9 | CCC | CC | | 10 | CCC | CC | | 11 | CCC | CC | | 12 | DDD | DD | | 13 | TTT | EE | | 14 | DDD | CC | +----+------+------+ -- Creating a secondary table with concatenated columns create table selected as select id,concat(city,cc) tmpfield from yourtable; -- Creating other table from based on latest table. create table selected2 as select id,count(tmpField) Total from selected group by tmpfield; select * from selected2; +----+-------+ | id | Total | +----+-------+ | 1 | 3 | | 4 | 4 | | 8 | 4 | | 14 | 1 | | 12 | 1 | | 13 | 1 | +----+-------+ -- Finally filter the redundant columns select a.id,a.city,a.cc from yourtable a,selected2 b where a.id = b.id; +----+------+------+ | id | city | cc | +----+------+------+ | 1 | AAA | AA | | 4 | BBB | BB | | 8 | CCC | CC | | 14 | DDD | CC | | 12 | DDD | DD | | 13 | TTT | EE | +----+------+------+
