Hello
Some SQL guru please help me. I have bashed my brains using GROUP BY
and HAVING but no luck.
I have this table:
id pn pf qty Loc
1 1234 1 1 aa
3 222 0 2 bb
4 222 1 2 cc
5 444 0 1 dd
6 444 0 2 ee
2 321 0 3 ff


I need to get back the following row:
id pn pf qty
1 1234 1 1

The logic is this. i need to get back any row that only has 1 unique
part number left (pn) in which the pf (partial flag) is set to 1.
basically its for a report to be generated once there is only 1
partial part left.

In the above case above, there are two rows with pn 222, one is a full
part and the other is a partial part. so 222 is not returned
pn 444 has two rows both are full parts so no need for it either.
321 is also a full part no need for it
however as you see pn 1234 is a partial part plus there are no other
rows that have pn 1234 so it will return it as the row.

I dont know if this is confusing but email me and I will explain
better.
THis is the closest I have gotten with no luck:

SELECT pf,pn, COUNT(*) as occ
FROM parts
group by pf,pn having pf=1 and count(*)=1

however it returns:
1 1234 1
1 222 1
Please help.
I can use stored proc and views

[quoted text, click to view]

David,
You rock!
Thanks a bunch. I had to cast the pf to a char before the Min worked.
but it was exactly what I needed!
Thanks
This is what I did:
SELECT MIN(id) AS id, pn,
1 AS pf,
MIN(qty) AS qty
FROM Parts
GROUP BY pn
HAVING MIN(CAST(pf AS CHAR(1)))=1

You nearly had it :)

SELECT MIN(id) AS id, pn,
1 AS pf,
MIN(qty) AS qty
FROM Parts
GROUP BY pn
HAVING MIN(pf)=1
AND COUNT(*)=1

--
David Portas
SQL Server MVP
--