none
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

    Monday, January 07, 2013 6:05 PM

Answers

  • ;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

    • Marked as answer by babbupunia Friday, January 11, 2013 4:15 PM
    Monday, January 07, 2013 6:20 PM
    Moderator

All replies

  • ;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




    Monday, January 07, 2013 6:08 PM
    Moderator
  • ;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

    • Marked as answer by babbupunia Friday, January 11, 2013 4:15 PM
    Monday, January 07, 2013 6:20 PM
    Moderator