It is possible to do what you want is a single query.

;with cte as( select [ctrc_num], [Ctrc_name],[docu],[adj],[new], [some_date] ,ROW_NUMBER() over(partition by [ctrc_num] -- group by [ctrc_num] order by [new], --0 then 1 [some_date] desc --newest first ) rn from #StackOverFlow ) delete cte where rn>1 ; select * from #StackOverFlow 

It is possible to do what you want is a single query.

;with cte as(  select [ctrc_num], [Ctrc_name], [docu],[adj], [new], [some_date]  ,ROW_NUMBER() over(partition by [ctrc_num] -- group by [ctrc_num]   order by [new], --0 then 1   [some_date] desc --newest first   ) rn  from #StackOverFlow ) delete cte where rn>1 ; select * from #StackOverFlow