Selecting records with identical categories, but opposing integers

user_12054903 0
Viewed 32

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:

DESIRED OUTPUT

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.

greatest-n-per-group ssms tsql sql-server sql
2 Answers

Hmmm . . . I think you want:

select t.*
from #temp t
where t.integerA < 0 or
      not exists (select 1
                  from #temp t2
                  where t2.A = t.A and t2.B = t.B and
                        t2.C = t.c and t2.integerA < 0
                 );

Here is a db<>fiddle.

user_11440350

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
user_106767160
Related