at first ,You d better to show the expected result。
And there are a few questions about your requirement:
1、 Does the part column always only have the value 1 and 3, or any other value : eg 2,4..
2、Are there any rows which could be with part = 1 and actn_ind = 'D'。If there are, how
to deal with these rows?
3、Does the actn_ind column always only have the values 'null' and 'D'?
4、Does each group only have at most 2 rows ?
I think your rules can be simplified to this :
1、pick up one max timestamp row from each partition group
2、if the group contains the row with part = 3 and actn_ind = 'D' then discard the whole group
3、if the group only contains the row with part = 1 ,then pick up one using rule 1。
I will give you a test sql, you can try this:
select Part, field1, field2, field3, field4, field5, field6, Actn_ind ,TS
,max(part) over (partition by field1,field2,field3) as max_part
,max(actn_ind) over (partition by field1,field2,field3) as max_actn_ind
,row_number()over (partition by field1,field2,field3 order by ts desc) as rn
) where rn =1 and (( max_actn_ind <> 'D' or max_actn_ind is null )
or ( max_part = 1 ))