--时间存在相同的，取金额最大的一个
with tb as
(
    select '1XP02503' as SERIAL_NUM,'TO_SUB_INV_CODE=ECP;TO_AREA_CODE=CP;' as ATTR_DATA,2820 as TRANSACTION_ID,'2012-11-10' as TRANSACTION_DATE union all
    select '1XP02503','TO_SUB_INV_CODE=SIE;TO_AREA_CODE=TLS;',1996,'2012-11-10' union all
    select '1XP02503','FAMILY=9TE;PART_NUM=9TE066-999;',1983,'2012-10-11' union all
    select '1XP02506','TO_SUB_INV_CODE=ECP;TO_AREA_CODE=CP;',3344,'2012-10-11' union all
    select '1XP02506','FAMILY=9TE;PART_NUM=9TE066-999;',1345,'2012-10-11'
),tb2 as 
(
    select 
        *,
        ROW_NUMBER() over(partition by Serial_num order by TRANSACTION_DATE desc,TRANSACTION_ID desc) as v_rank
    from tb
    where CHARINDEX('TO_SUB_INV_CODE',ATTR_DATA)>0
        and 
        CHARINDEX('TO_AREA_CODE',ATTR_DATA)>0
        and TRANSACTION_DATE=
        (
            select MAX(TRANSACTION_DATE) from tb t where t.SERIAL_NUM=tb.SERIAL_NUM
        )
)
select * from tb2
where v_rank='1'
/*
SERIAL_NUM ATTR_DATA                             TRANSACTION_ID TRANSACTION_DATE v_rank
---------- ------------------------------------- -------------- ---------------- --------------------
1XP02503   TO_SUB_INV_CODE=ECP;TO_AREA_CODE=CP;  2820           2012-11-10       1
1XP02506   TO_SUB_INV_CODE=ECP;TO_AREA_CODE=CP;  3344           2012-10-11       1

(2 row(s) affected)
*/