SQL: Deleting row which values already exist

Question

I have a table that look like this:

ID | DATE       | NAME   | VALUE_1 | VALUE_2
1  | 27.11.2015 | Homer  | A       | B
2  | 27.11.2015 | Bart   | C       | B
3  | 28.11.2015 | Homer  | A       | C
4  | 28.11.2015 | Maggie | C       | B
5  | 28.11.2015 | Bart   | C       | B

I currently delete duplicate rows (thank to this thread) using this code :

WITH cte AS
(SELECT ROW_NUMBER() OVER (PARTITION BY [VALUE_1], [VALUE_2]
ORDER BY [DATE] DESC) RN
FROM [MY_TABLE])
DELETE FROM cte
WHERE RN > 1

But this code don't delete exactly the lines I want. I would like to delete only rows which values already exist so in my example I would like to delete only line 5 because line 2 have the same values and is older.

Code to create my table and insert values:

CREATE TABLE [t_diff_values]
([id] INT IDENTITY NOT NULL PRIMARY KEY,
[date] DATETIME NOT NULL,
[name] VARCHAR(255) NOT NULL DEFAULT '',
[val1] CHAR(1) NOT NULL DEFAULT '',
[val2] CHAR(1) NOT NULL DEFAULT '');

INSERT INTO [t_diff_values] ([date], [name], [val1], [val2]) VALUES
('2015-11-27','Homer',  'A','B'),
('2015-11-27','Bart',   'C','B'),
('2015-11-28','Homer',  'A','C'),
('2015-11-28','Maggie', 'C','B'),
('2015-11-28','Bart',   'C','B');

Answer 1

You need to add one more CTE where you will index all islands and then apply your duplicate logic in second CTE:

DECLARE @t TABLE
    (
      ID INT ,
      DATE DATE ,
      VALUE_1 CHAR(1) ,
      VALUE_2 CHAR(1)
    )

INSERT  INTO @t
VALUES  ( 1, '20151127', 'A', 'B' ),
        ( 2, '20151128', 'C', 'B' ),
        ( 3, '20151129', 'A', 'B' ),
        ( 4, '20151130', 'A', 'B' );
WITH    cte1
          AS ( SELECT   * ,
                        ROW_NUMBER() OVER ( ORDER BY date)
                        - ROW_NUMBER() OVER ( PARTITION BY VALUE_1, VALUE_2 ORDER BY DATE) AS gr
               FROM     @t
             ),
        cte2
          AS ( SELECT   * ,
                        ROW_NUMBER() OVER ( PARTITION BY VALUE_1, VALUE_2, gr ORDER BY date) AS rn
               FROM     cte1
             )
    DELETE  FROM cte2
    WHERE   rn > 1

SELECT  *
FROM    @t