select values from a table where column values should be different
Well, condition2 seems to cover condition1 and condition3 too. What you probably need would be something like:

;WITH CTE AS
(
    SELECT  *,
            N = RANK() OVER(PARTITION BY cpt, cpt4 ORDER BY eff_date DESC)
    FROM dbo.YourTable
)
SELECT *
FROM CTE
WHERE N = 1;


This should cover everyone of your conditions.