Eirikur Eiriksson (3/31/2014)
Luis Cazares (3/31/2014)
Here are 2 other options.
WITH Contacts AS(
SELECT INS.INSPNO, CNT.FIRSTNAME, CNT.LASTNAME, CNT.CAPACITY,
ROW_NUMBER() OVER( PARTITION BY INS.INSPNO ORDER BY CASE WHEN Capacity = 'Contractor' THEN -1 ELSE INS.INSPNO END DESC) rn
FROM @inspection INS
INNER JOIN @INSPECTIONCONTACT CNT
ON INS.INSPNO = CNT.INSPNO
)
SELECT INSPNO, FIRSTNAME, LASTNAME
FROM Contacts
WHERE CAPACITY <> 'Contractor'
OR rn = 1;
Short, sweet and about the same cost as my code, respect! 🙂
It might slow down if there are too many contractors. But it's worth the try.
The important thing is that the OP tests under the real conditions.