删除重复的旧行并保留最新的mysql

Question

I have a table

id:name:passportnumber:score
1:xxx:123456:99
2:yyy:789012:88
3:xxx:123456:11
4:xxx:123456:44
5:xxx:123456:66
6:xxx:123456:77

I want to delete duplicated old rows and keep only latest one which has same passport number

id:name:passportnumber:score    
2:yyy:789012:88
6:xxx:123456:77

What is the best way without temp table

Thanks

Answer 1

You should be able to do a self-join to your table and perform the delete:

delete t1
from yt t1
inner join yt t2
  on t1.passportnumber = t2.passportnumber
where t1.id < t2.id;

See SQL Fiddle with Demo