Delete duplicate row with max date

Question

Hi,

I have a table MemBER_Claims with fowwing columns:

MemberID	Claim Date	P Code	Cost	T Code	Cycle
4036	7/30/2010	P	22	A	1
4036	8/9/2010	D	4	B	1
4036	9/24/2010	U	24	B	1
4036	4/5/2011	P	27	A	2
4036	4/26/2011	F	32	B	2
4036	8/29/2011	F	45	A	3
4036	9/7/2011	U	15	B	3

I have to get rid of row with date 9/24/2010. That is, If there are two consecutive T-code 'B' followed by 'A', then it should pick only the row with T-code 'B' with min claimdate.

Any help is appreciated.

Thanks,

Punia

Answer 1

;with cte as (select *, row_number(0 over (partition by MemberId, [T Code], [Cycle] order by [Claim Date]) as Rn from dbo.Member_Claims)
delete from cte where Rn > 1 -- delete all extra rows per the same MemberId and [T Code] 
--(e.g. if we have 2 or more rows with code B all rows will be deleted expect for the row with the earliest date).

---

For every expert, there is an equal and opposite expert. - Becker's Law


My blog

Answer 2

;with mycte as (select *, row_number() over (partition by MemberID, [T Code], Cycle order by [Claim DATE]) as rn 
From dbo.Member_Claims)
SELECT * FROM mycte where rn=1