Perhaps this?
USE [test]
go
select
ComponentId,
partRefId = MIN(partRefId),
Name,
DocumentId,
SheetRef = MIN(SheetRef),
LocationId,
Effectivity,
EffectivityId,
DesignatorPrefix,
Designator,
DesignatorSuffix
FROM PartRef pr
JOIN PartDef pd
ON pd.PartDefId = pr.PartDefId
Where pd.PartClass = 'Component' AND pr.EffectivityId = '11'
group by
ComponentId,
Name,
DocumentId,
LocationId,
Effectivity,
EffectivityId,
DesignatorPrefix,
Designator,
DesignatorSuffix
Very cool! it does precisely what I want. It is also clear and concise, I was afraid I would have to use some difficult convoluted script with multiple joins, temp tables, sub queries and a trip to pluto. but this is elegant. I had not yet seen a select statement written in such a way.
Thank You!