0

I am looking to write a function to remove a row from the below table if the reverse client pair exists in the table (eg if cd1/cd2 has a cd2/cd1 matching pair)

I would like to remove the oldest record and keep the newest. To make it more clear I have included the table + the expected result

Table

flip (`dt`cd1`cd2`time`adj)!(2020.10.28 2020.10.28 2020.10.28 2020.10.28 2020.10.28 2020.10.28 2020.10.28;`ENC3`FA01`ENC4`STK2`STK1`RND3`TST5;`ENC4`FA02`ENC3`STK1`STK2`RND4`TST6;`s#2020.10.26T11:52:06.466 2020.10.26T12:52:06.466 2020.10.27T12:55:07.545 2020.10.27T16:08:34.051 2020.10.27T21:55:15.873 2020.10.28T12:59:06.466 2020.10.28T14:58:06.466;0.15 0.2 0.5 0.01 0.09 0.03 0.7)

Expected result below

flip (`dt`cd1`cd2`time`adj)!(2020.10.28 2020.10.28 2020.10.28 2020.10.28 2020.10.28;`FA01`ENC4`STK1`RND3`TST5;`FA02`ENC3`STK2`RND4`TST6;2020.10.26T12:52:06.466 2020.10.27T12:55:07.545 2020.10.27T21:55:15.873 2020.10.28T12:59:06.466 2020.10.28T14:58:06.466;0.2 0.5 0.09 0.03 0.7)

Really appreciate any input! Thanks.

Will Da Silva
  • 6,386
  • 2
  • 27
  • 52
pmade1
  • 21
  • 4

1 Answers1

3

One way to keep the most recent row is:

select from t where time=(last;time) fby (asc each cd1,'cd2)

In above query rows are grouped by alphabetically sorted (cd1;cd2). Then row with the latest time is selected within each group. If table is not sorted by ascending time, replace last with max.

For deleting the oldest rows and keeping rest, e.g. when there are more than two rows with same instruments, use:

delete from t where ({(x=min x)&1<count x};time) fby (asc each cd1,'cd2)
Anton Dovzhenko
  • 2,399
  • 11
  • 16