Selecting records with identical categories, but opposing integers

Question

This is the nature of the table I am working with:

IF OBJECT_ID('TEMPDB..#TEMP') IS NOT NULL
  DROP TABLE #TEMP

CREATE TABLE #TEMP (
  CategoryA         NVARCHAR(10),
  CategoryB         NVARCHAR(10),
  CategoryC         NVARCHAR(10),
  IntegerA          INT,
);

INSERT INTO #TEMP(CategoryA,CategoryB,CategoryC,IntegerA)
VALUES  
('A','H','G',20),
('A','H','G',-15),
('F','L','C',10),
('N','U','X',12),
('K','G','G',15),
('K','G','G',-10);

SELECT * FROM #TEMP

Notice that the top 2 rows and the bottom 2 rows have identical categories, however they have integers of opposite polarity. The middle 2 rows are distinct with positive integers.

I need a way to select all of the records that are not duplicated (Such as the middle 2 rows). And I need to select the records with negative integers, without selecting their positive counter-parts.

The desired output in this case would be:

I have tried seeing if I can make my own table which inserts only the records I want, but I run into the same problem again where I cannot figure out how to distinguish between the records where all of the categories are the same.

Answer 1

For this dataset, you could just use row_number():

select categoryA, categoryB, categoryC, integerA
from (
    select 
        t.*, 
        row_number() over(partition by categoryA, categoryB, categoryC order by integerA) rn
    from temp t
) t
where rn = 1