CODEWITHSUNDEEP
kdb+
pmade1
pmade1

Reputation: 21

Delete the oldest record from KDB table based on client pair match

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.

Upvotes: 0

Views: 128

Answers (1)

Anton Dovzhenko
Anton Dovzhenko

Reputation: 2569

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)

Upvotes: 3

Related Questions