SQL Server data grouping-CodePudding

Good evening folks, This probably would be simple for a lot of folks out there, but I am struggling on this problem for a couple of days now.

I have a table, lets call it dbo.RawDump table. On this table, I have the columns STKNBR and SaleTypeID.

Sample Data:

STKNBR                           SaleTypeID
1010186732                        2
1010186732                        1
1010188780                        2
1010190707                        1
1010190707                        2
1010190350                        2
1010190446                        2
1010190647                        2

What I am trying to figure out is how to only pick out the STKNBR's who have a SaleTypeID of 2. I dont want to pickup the ones which have a saletypeID of 1 and 2. The result should give me only those STKNBR's that have a SaleTypeID of only 2.

What I have tried so far:

SELECT STKNBR, SaleTypeID  FROM dbo.RawDump lm 
WHERE  lm.SaleTypeID = 2 AND lm.SaleTypeID <> 1

I understand that this is probably a silly question, but any help is appreciated to overcome this.

Thanks for reading!

CodePudding user response：

It's fairly simple:

SELECT
    STKNBR
FROM dbo.RawDump
GROUP BY STKNBR
HAVING MIN(SaleTypeId) = 2 AND MAX(SaleTypeId) = 2

CodePudding user response：

Depending on what you want if there is a row SaleTypeId = 3, you may want this

SELECT
    rd.STKNBR
FROM dbo.RawDump rd
GROUP BY
  rd.STKNBR
HAVING COUNT(CASE WHEN rd.SaleTypeId = 1 THEN 1 END) = 0
   AND COUNT(CASE WHEN rd.SaleTypeId = 2 THEN 1 END) > 0;