delete from fightrank where rank not in (select rank from (select * from fightrank where actorid in (select actorid from (select count(1) total,actorid from fightrank group by actorid having total>1)tmp ) order by rank asc) tmp1 group by actorid) and actorid in (select actorid from (select count(1) total,actorid from fightrank group by actorid having total>1)tmp);