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
Advertisement
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