>In this case:
>
>

>DECLARE @test table (ClientId int, DateAdded date, cStatus varchar(10))
>
>insert into @test (ClientId, DateAdded, cStatus) 
>
>values (1, '20150101', 'Created'), (1, '20150102', 'Uploaded'), (1, '20150103', 'Completed'),
>(1, '20150104', 'Completed'),
>(1, '20150105', 'Assigned'),
>(1, '20150106', 'Completed'), (1, '20150107', 'Completed'), (1, '20150108', 'Completed')
>
>
>;with cteIslands as (select *, row_number() over (partition by clientId order by DateAdded DESC) as RowNumber, -- this column can be removed, used just for tests
>
>row_number() over (partition by clientId order by DateAdded DESC) - 
>row_number() over (partition by clientId, cStatus order by DateAdded DESC) as IslandGroup
>from @test ),
>cteCandidates as (select *, row_number() over (partition by clientId, IslandGroup order by DateAdded) as ReversedOrder from cteIslands where IslandGroup = 0 and cStatus = 'Completed')
>
>select * from cteCandidates where ReversedOrder > 1 -- rows to delete

>
>Also, my from the top of my head solution was wrong, I should have used partition by status (or case based status), not the order by it.

Thanks