Deleting duplicate rows with condition I have posted a similar question before, but this one is a little modified.

I have a table with 4 columns as shown below.


ITEMCHARGE     CPT4       EFF_DATE    CPT4MOD
326.8         29075       20101204    GO
326.8         29075       20110104    GO
326.8         29075       20110204    GO
326.8         29075       20110406    GO
352.69        29075       20090611    GO
352.69        29075       20090917    GO
352.69        29075       20100614    GO
352.69        29075       20100722    GO



Now, if CPT4 and CPT4MOD match, I need to keep the rows with distinct ITEMCHARGE (with latest EFF_DATE). The output should be like below:

    ITEMCHARGE  CPT4    EFF_DATE    CPT4MOD
       326.8    29075   20110406    GO
       352.69   29075   20100722    GO


There are 2 distinct ITEMCHARGE for the same CPT4 and CPT4MOD. So we took the latest EFF_DATE row for both of them. It would be very helpful If I can get a linq (with lamda) and SQL version for this.
Would that work? SQL version only.

SELECT *
FROM
(
SELECT ROW_NUMBER() OVER (PARTITION BY CPT4, CPT4MOD, ITEMCHARGE ORDER BY EFF_DATE DESC) AS RowNo
    , *
FROM #table
) AS T
WHERE T.RowNo = 1


You can check it here:
http://data.stackexchange.com/stackoverflow/query/260027/delting-duplicate-rows-with-condition