CODEWITHSUNDEEP
t-sql
Alan Fisher
Alan Fisher

Reputation: 2055

How Do I find which rows don't have at least one column marked true, grouped by another column

I have a table for tracking PartNumber information for a Bin database. Each Bin can have many Partnumbers but at least one PartNumber must be marked as the Primary for that Bin. In my table of PartNumbers there are many records that have never been identified as being the primary and I need to be able to identify them so the data can be scrubbed before being able to enforce the business rule. I have looked at OVER(PARTION BY) but I can not figure out how to get just those records that don't have at least one PartNumber marked as primary.

Table Sturcture:

PartNumbnerID PK int(IDENTITY)
PartNumber varchar(20)
BinNumber char(5) FK 
BinPrimary bit

Sample Data:

PartNumberID  PartNumber    BinNumber    BinPrimary

1             123           22222          0
2             345           22222          0
3             456           33333          0
4             678           33333          0
5             789           44444          1
6             890           44444          0

Results of query would be:

BinNumber
 22222
 33333

Upvotes: 0

Views: 37

Answers (2)

t-clausen.dk
t-clausen.dk

Reputation: 44336

This could be an advanage when you need more columns, however if you only need Binnumber, you should go with Wolf's solution

SELECT PartNumberID,PartNumber,BinNumber,Primary FROM 
(
SELECT 
PartNumberID,PartNumber,BinNumber,Primary,
max(primary) over(partition by BinNumber) chk
FROM table
) a
WHERE chk = 0

Upvotes: 1

Cristian Lupascu
Cristian Lupascu

Reputation: 40566

It's as simple as:

select BinNumber
from MyTable
group by BinNumber
having max([Primary]) = 0

Here's an SqlFiddle: http://www.sqlfiddle.com/#!3/c1d25/2

Upvotes: 2

Related Questions