SELECT ship_id,shipnaming_name ,shipnaming_number ,cid
FROM ship_naming_comsostav t1
inner JOIN (SELECT ship_id AS sid, count(*) AS cid
FROM ship_naming_comsostav GROUP BY sid HAVING count(*)>1) t2 ON t1.ship_id = t2.cid

select a.*
from ship_naming_comsostav a
inner join (
    select ship_id,max(shipnaming_number) as shipnaming_number
    from ship_naming_comsostav group by ship_id) b
using(ship_id,shipnaming_number)