select values from a table where column values should be different Let us say there are 4 columns in a table .

cpt,cpt4mod,itemcharge,eff_date.


condition1: if the values of cpt,cpt4 and itemcharge are same, but eff_date is different. Then I need to fetch the latest record based on eff_date.

condtition2: if the values of cpt and cpt4 are same, but eff_date and itemcharge are different. Then I need to fetch the latest record based on eff_date.

condtition3:if all columns have the same values, then I need to fetch the latest record based on eff_date

condtition4: if the values of cpt,cpt4and,eff_date are same  butitemcharge  is different. Then I need to fetch all the records except the latest one
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.