IF OBJECT_ID('tempdb..#foo') IS NOT NULL DROP TABLE #foo
CREATE TABLE #foo (
	[bar] CHAR(1)
	, [val] INT
	)

INSERT #foo ( bar, val )
VALUES      ( 'A', 1)
		  , ( 'A', 2)
          , ( 'B', 2)
          , ( 'A', 3)
      
; WITH delCTE AS (
	SELECT *
		, ROW_NUMBER() OVER (PARTITION BY [bar] ORDER BY [val]) AS [rnk]
	FROM #foo
	)
DELETE d OUTPUT DELETED.* FROM delCTE AS d WHERE d.[rnk] > 1

SELECT * FROM #foo

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.

 WITH delCTE AS (   SELECT *    , ROW_NUMBER() OVER (PARTITION BY [bar] ORDER BY [val]) AS [rnk]   FROM #foo   )  DELETE d OUTPUT DELETED.* FROM delCTE AS d WHERE d.[rnk] > 1

  |--Sequence Project(DEFINE:([Expr1009]=row_number))
       |--Segment
            |--Sort(ORDER BY:([tempdb].[dbo].[#foo].[bar] ASC, [tempdb].[dbo].[#foo].[val] ASC))
                 |--Table Delete(OBJECT:([tempdb].[dbo].[#foo]))
                      |--Top(ROWCOUNT est 0)
                           |--Filter(WHERE:([Expr1004]>(1)))
                                |--Sequence Project(DEFINE:([Expr1004]=row_number))
                                     |--Segment
                                          |--Sort(ORDER BY:([tempdb].[dbo].[#foo].[bar] ASC, [tempdb].[dbo].[#foo].[val] ASC))
                                               |--Table Scan(OBJECT:([tempdb].[dbo].[#foo]))

Msg 3903.. The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION.