This anwer works for the given data but wouldn't work if we inserted a row like:

insert into #new(id,keyvalue) values (1,'bb')

The stated answer assumes a duplicate is defined by the ID column and would delete the above row even thought it's unique in the table. Since no PK is defined on the table I think we must assume a duplicate is defined by multiple rows with identical values for every column. So if we include both ID and keyvalue columns in the over() clause we will delete only non-unique rows:

with numbered as

(

SELECT rowno=row_number() over (partition by ID, keyvalue order by ID, keyvalue),ID,keyvalue

from #new

)

delete from numbered where rowno>1