sqlsql-server

SQL Server data grouping

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

Any help is appreciated to overcome this.

Solution

  • It's fairly simple:

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