How can I delete records that have the same values in two non-primary key columns while factoring in a third?

Here is a link to a sqlfiddle with sample data: http://sqlfiddle.com/#!18/d8d552/3

I’m trying to delete 1 row from rows that have the same values in both name and color. If they have they same score, then it doesn’t matter which one is deleted. If those rows have a different score, then I want to delete the row with the lower score.

I’m getting it down to the correct rows (but with an extra row with the same score that I would want to keep one of) using an inner join, but I’m having difficulty differentiating between scores.

Another route I’ve tried is with the 2nd query that gets me the exact rows I want to delete, but then I can’t seem to get the ItemID as it’s not part of the aggregate function or GROUP BY. Using the function ROW_NUMBER() helps get the correct rows, but makes it difficult to get the IDs to delete.

I’m looking to delete ItemIds 1 and 9 and then 1 of (2,4) and 1 of (6,8)

ItemID	name	color	score
1	asdf	green	5
2	asdf	blue	4
3	asdf	green	6
4	asdf	blue	4
5	asdf	yellow	0
6	qwer	green	3
7	qwer	blue	3
8	qwer	green	3
9	qwer	blue	2
10	qwer	yellow	0

Here is some SQL that I’ve tried out which gets me close:

SELECT I.*
FROM Items I
INNER JOIN 
    (SELECT name, color, MIN(score) AS ms
     FROM Items
     GROUP BY name, color
     HAVING COUNT(*) > 1) s ON I.name = s.name 
                            AND I.color = s.color 
                            AND I.score = ms
ORDER BY I.ItemID
GO

SELECT 
    name, color, MIN(score) AS minScore, 
    ROW_NUMBER() OVER (PARTITION BY name, color
                       ORDER BY MIN(score)) AS rn
FROM Items
GROUP BY name, color
HAVING COUNT(*) > 1
ORDER BY name

---

EDIT

I believe @Susang ‘s answer works better than mine. Here’s a link to a fiddle with it working: http://sqlfiddle.com/#!18/d8873/5

Answer

You can use CTE as below:

CREATE TABLE #test(ItemID INT,  name varchar(50),   color varchar(50),  score INT)
INSERT INTO #test(ItemID, name, color, score) VALUES
(1,     'asdf',     'green',    5),
(2,     'asdf',     'blue',     4),
(3,     'asdf',     'green',    6),
(4,     'asdf',     'blue',     4),
(5,     'asdf',     'yellow',   0),
(6,     'qwer',     'green',    3),
(7,     'qwer',     'blue',     3),
(8,     'qwer',     'green',    3),
(9,     'qwer',     'blue',     2),
(10,    'qwer',     'yellow',   0)

;WITH recDelete AS (
    SELECT 
        rnk = ROW_NUMBER() OVER (PARTITION BY name, color ORDER BY score DESC),
        ItemID
    FROM #test
)
DELETE FROM recDelete WHERE rnk > 1