You can get what you originally asked for with SQL, though since more than one row with the same name can have the same score, you might get more than one row with the same name/score and a different id. Using your data:
sqlite> select * from scores;
1|John|24
2|John|60
3|Mary|4
4|John|10
5|Kate|30
6|Mary|20
sqlite> select a.id, a.name, a.score
from scores a
join ( select name, max(score) as "score" from scores group by name )
+b
on a.name = b.name and a.score = b.score;
2|John|60
5|Kate|30
6|Mary|20
sqlite> insert into scores values(7, 'John', 60);
sqlite> select a.id, a.name, a.score
from scores a
join ( select name, max(score) as "score" from scores group by name )
+b
on a.name = b.name and a.score = b.score;
2|John|60
5|Kate|30
6|Mary|20
7|John|60
I couldn't figure out has to express that in DBIx::Class.
