Deleting duplicate rows with condition(Using SQL and LINQ)
WITH KeepLatest AS
(
  SELECT *, 
     RANK() OVER (
       PARTITION BY CPT4,CPT4Mod
       ORDER BY Eff_Date DESC, ItemCharge) as Rnk
  FROM dbo.YourTable
 )
 DELETE KeepLatest
 WHERE Rnk &gt; 1
 ;


By using RANK() instead of ROW_NUMBER() you will avoid deleting anything if you have a row which is identical across all four columns - although I'm not sure why you'd want that.

If you know the CPT4Mod value, include: AND CPT4Mod = @CPT4Mod