Eliminate rows with opposite values

Question

I would like to exclude rows with opposite values, for example I have the following rows in my table

id     value1  value2  value3
----   ------  ------  -------
10     -1        -20     -48.5
10      1         20      48.5
10     -1        -30     -26.2
10      1         30      26.2
10      1         27      30.5

what I would like to receive

id     value1  value2  value3
----   ------  ------  -------
10      1         27      30.5

Any suggestions would be greatly appreciated.

Answer 1

I return positive numbers. Then I group all the fields, returning only single-line ones. You can use MINor MAXto select. Both will return the correct result.

DECLARE @Tbl TABLE (id INT, value1 INT, value2 INT, value3 FLOAT)

INSERT INTO @Tbl        
VALUES 
(10,     -1,        -20,     -48.5),
(10,      1,         20,      48.5),
(10,     -1,        -30,     -26.2),
(10,      1,         30,      26.2),
(10,      1,         27,      30.5)


SELECT
    T.id ,
    MIN(T.value1) ,
    MIN(T.value2),
    MIN(T.value3)
FROM
    @Tbl T
GROUP BY
    T.id ,
    ABS(T.value1) ,
    ABS(T.value2),
    ABS(T.value3) HAVING COUNT(1) = 1

Result:

id  value1  value2  value3
10  1       27      30.5

I recommend that you check it for larger data. You may have performance issues.

Answer 2

How about this:

DECLARE @T TABLE (
    id INT,
    value1 INT,
    value2 INT,
    value3 DECIMAL(9,2)
)

INSERT @T VALUES
(10, -1, -20, -48.5),
(10, 1, 20, 48.5),
(10, 1, -20, -48.5),
(10, -1, 20, 48.5),
(10, -1, -30, -26.2),
(10, 1, 30, 26.2),
(10, 1, 27, 30.5)

SELECT A.* FROM @T A
LEFT JOIN @T B ON
    B.id = A.id
    AND B.value1 = -A.value1
    AND B.value2 = -A.value2
    AND B.value3 = -A.value3
WHERE B.id IS NULL