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.

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

Later I need to achieve the same using LINQ.

WITH KeepLatest AS
  SELECT *, 
     RANK() OVER (
       ORDER BY Eff_Date DESC, ItemCharge) as Rnk
  FROM dbo.YourTable
 DELETE KeepLatest
 WHERE Rnk > 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


You can learn from this example :

create table dbo.ProductsCount (prdid int , cnt int)

insert into dbo.ProductsCount (prdid, cnt)
select 111,2
union all 
select 111,5
union all
select 112,10
union all
select 112,20

select *
from dbo.ProductsCount
order by prdid;

-- delete duplicates with CTE and ROW_NUMBER()

    select prdid,cnt,ROW_NUMBER() over (PARTITION BY prdid order by (select null) ASC) RN
    from dbo.ProductsCount
delete C 
where RN > 1
  • Not able to replicate my scenario with above example. Can you bring more clarity to your answer. – Rajesh Dec 21 '14 at 18:00
  • do you want to delete duplicate rows ? – itzik Paz Dec 21 '14 at 18:04
  • Yes, but I want to leave the one with latest Eff_Date. One more thing(the deletion criteria is that first two columns should match, but the later two should be different compulsorily) – Rajesh Dec 21 '14 at 19:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.