This works for me. I've made a few assumptions as to the ID, value and name.

I've also modified the test data for assumption 1 and 2.

1. ID value is not always incremented by 1 there can be gaps.

2. Value does not always have the same name (i.e. 10 is not always "test")

3. We're looking for duplicates in Value and Test (i.e. composite unique key)

4. We keep the first instance of a duplicate and delete the subsequent ones.

DECLARE @testtable TABLE (id INT, value INT, NAME CHAR(4));

INSERT INTO @testtable

VALUES

(1, 10, 'test'), --Keep

(2, 5, 'prod'), --Keep

(4, 10, 'test'), --Keep

(6, 4, 'test'), --Keep

(7, 4, 'test'), --Drop

(9, 4, 'Job'), --Keep (matching value but not name)

(11, 10, 'test'), --Keep

(13, 10, 'test') --Drop

;

with t1 as (

select ROW_NUMBER()over(order by id) RowID

,ID

,Name

,Value

from @testtable

)

delete a

from @testtable a

where id in (

select t1.id

from t1

inner join t1 t2

on t1.RowID = t2.RowID + 1

and t1.value = t2.value

and t1.NAME = t2.NAME

)

select *

from @testtable