Adding Collation to a SQL Server CTE statement

Question

I am trying to determine how to add COLLATION to this operation....

WITH CTE (srtTxt, DuplicateCount)
AS
(
    SELECT 
       srtTxt,
       ROW_NUMBER() OVER(PARTITION BY srtTxt ORDER BY ID) AS DuplicateCount
    FROM dbo.tblLang
    **WHERE DuplicateCount > 1**
 )
 DELETE
 FROM CTE
 WHERE DuplicateCount > 1
 GO

This is the setting I am trying to apply...

COLLATE Latin1_General_CS_AS srtTxt 

I have tried a variety of changes like this....

WITH CTE (srtTxt, DuplicateCount)
AS
(
    SELECT 
       srtTxt,
       ROW_NUMBER() OVER(PARTITION BY srtTxt ORDER BY ID) AS DuplicateCount
    FROM 
       (SELECT srtTxt COLLATE Latin1_General_CS_AS AS srtTxt
        FROM dbo.tblLang) AS T
       **WHERE DuplicateCount > 1**
 )
 DELETE
 FROM CTE
 WHERE DuplicateCount > 1
 GO  

Any ideas on how to implement this?

Answer 1

Given that you are deleting, you don't need to select srtTxt other than the partition, so all that is needed is:

WITH CTE
AS
(
    SELECT 
       ROW_NUMBER() OVER(PARTITION BY srtTxt COLLATE Latin1_General_CS_AS 
                         ORDER BY ID) AS DuplicateCount
    FROM dbo.tblLang
 )
 DELETE
 FROM CTE
 WHERE DuplicateCount > 1; 

However, if you did need to also select the re-collated field, in order to prevent repetition of the collation in the Select and Partition, you can DRY this up with a preceding CTE:

WITH Collated AS 
( 
   SELECT ID, srtTxt COLLATE Latin1_General_CS_AS srtTxt
   FROM dbo.tblLang
),
Duplicates(srtTxt, DuplicateCount)
 AS
 (
    SELECT srtTxt,
    ROW_NUMBER() OVER(PARTITION BY srtTxt ORDER BY ID) AS DuplicateCount
    FROM Collated
 )
 DELETE
 FROM Duplicates
 WHERE DuplicateCount > 1; 

Fiddle

Answer 2

I see that you missed to select ID in the sub-query Please add ID too as follows

;WITH CTE (srtTxt, DuplicateCount)
AS (
    SELECT 
    srtTxt,
    ROW_NUMBER() OVER(PARTITION BY srtTxt ORDER BY ID) AS DuplicateCount
    FROM (
    SELECT 
    ID, srtTxt COLLATE Latin1_General_CS_AS AS srtTxt
    FROM dbo.tblLang
    ) AS T
 )
 DELETE
 FROM CTE
 WHERE DuplicateCount > 1
 GO