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.