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;