Max and FirstSortedValue across two fields

[shane_spencer]

PFA my sample data. The requirement is fairly simple.

For each [Transaction Reference] I want 1 row. | want the field value returned based on firstly the Max [CapitalFlag] then then Max [Nett Value]. i.e. No matter what the max Nett Value is the higher CapitalFlag (i.e. 8 not 1) will take presidence.

So for this example it would be the second row. Even thought the Nett Value isn't highest the Capital Flag is higher (8 not 1).

I started with this script but it takes into account just the Nett Value, not the CapitalFlag