Skip to content
Advertisement

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:


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:

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement