If you like using aggregates (all those MINs suggest that) and are not too concerned by the idea of using two queries instead of one, and that "having the most data" can be changed into "having the lowest ID" as suggested by your code, something that will work in SQL 2000 is

delete results R where not exists (

select 1 from (

select A.ScrubID, max(A.Score) as Score from results A group by ScrubID

) MX

where MX.Score = R.Score and MX.ScrubId = R.ScrubID); -- gets rid of low scores

delete results R where R.ID not in (select min(A.ID) from results A group by scrubID) ; -- gets rid of duplicates with high IDs

Four years ago, I might have worked out how to do it in a single query in SQL 2000, but years of being spoilt by the neat new features in SQL 2008 and its successors have made me less willing (and probably less able, too) to write really convoluted queries.

Actually, Jeff's first suggestion does the same as my first query, and will probably do it a lot more efficiently unless there's some very unlikely (and rather silly) indexing.

edit: spelling :blush: