Deleting duplicate rows with condition(Using SQL and LINQ) I have a table with 5 columnsID,CPT4,CPT4Mod,ItemCharge,Eff_Date. 

Deletion Condition:

When rows exists with duplicate CPT4and CPT4Mod field values but different ItemCharge and Eff_Date values, exclude all but the most recent Eff_Date records.

I need a query which can achieve this in two scenarios.


When CPT4Mod is given by the user.
When  CPT4Mod is not given by the user.


Later I need to achieve the same using 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