CODEWITHSUNDEEP
sqlsql-servert-sql
Nick
Nick

Reputation: 105

Remove rows whose sum is equal to 0

For example I have a simple table with values:

|Code|Value|
|x1  |5    |
|x2  |-5   |
|x3  |-5   |
|x4  |5    |
|x5  |5    |

I need to remove rows whose sum is equal to 0. I.e. for this example I'll want to remove rows with codes x1 and x2, x3 and x4 OR x1 and x2, x3 and x5 - it doesn't matter in my case.

It sounds like simple task, but I can't do it.
I can't even understand how to select the items I want to delete.

I tried to join table on itself like this:

SELECT 
    t1.Code AS Code1,
    t2.Code AS Code2
FROM TableX AS t1
JOIN TableX As t2
ON t1.Code <> t2.Code
    AND t1.Value + t2.Value = 0
ORDER BY t1.Code

Result:

|Code1|Code2
|x1   |x2
|x1   |x3
|x2   |x1
|x2   |x4
|x2   |x5
|x3   |x1
|x3   |x4
|x3   |x5
|x4   |x2
|x4   |x3
|x5   |x2
|x5   |x3

But I don't understand what should I do next.

I'm not lazy - I tried different variants - with GROUP BY and MIN, with ROW_NUMBER() but unfortunately I can't understand how can I select rows to delete and leave others?

Upvotes: 1

Views: 1306

Answers (1)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239654

This seems to cover it, I believe:

declare @t table (Code varchar(20) not null, Value int not null)
insert into @t(Code,Value) values
('x1',5  ),
('x2',-5 ),
('x3',-5 ),
('x4',5  ),
('x5',5  )

;With Ordered as (
    select
        Code,
        Value,
        ROW_NUMBER() OVER (PARTITION BY Value ORDER BY Code) rn
    from
        @t
), Pairs as (
    select
        o1.Code c1,
        o2.Code c2
    from
        Ordered o1
            inner join
        Ordered o2
            on
                o1.Value = -o2.Value and
                o1.rn = o2.rn
    where
        o1.Value > 0
)
delete from t from @t t where exists (
    select * from Pairs p where p.c1 = t.Code or
                                p.c2 = t.Code)

select * from @t

Result:

Code                 Value
-------------------- -----------
x5                   5

This works be deciding to give unique row numbers to each row, based on what their Value is. We then pair the rows based on finding rows with equal row numbers but opposite values.

It may be instructive to select * from Ordered or select * from Pairs rather than the delete, if you want to see how it's working.

Upvotes: 4

Related Questions