Rohan.SQL (4/2/2012)
this should work fine...
SELECT UPPER(os.cpt4_id)
FROM #TRANS t
CROSS APPLY ( SELECT TOP 1 cpt4_id
FROM #outpatient_service
WHERE auth_id = t.auth_id
ORDER BY Key_id
) os
--WHERE auth_id = @auth_id
It will work, but the amount of scans is going to be much higher. I would go with the previous method.