select from table distinct syms based on newest data

Rezzy 0
Viewed 75

I have a table and I want to find out the most efficient way of filtering this table.

time              date         sym         Capture
12:00:00.000      2022.09.12   `AAPL       2022.09.12D15:30:00.000000000
10:00:00.000      2022.09.10   `MSFT       2022.09.10D11:20:00.000000000
14:00:00.000      2022.09.12   `AAPL       2022.09.12D14:20:00.000000000
0Nt               2022.09.11   `AAPL       2022.09.11D10:05:00.000000000
16:00:00.000      2022.09.11   `AAPL       2022.09.12D17:20:00.000000000
0Nt               2022.09.11   `MSFT       2022.09.11D11:30:00.000000000
0Nt               2022.09.11   `MSFT       2022.09.11D15:00:00.000000000

It has to be returned in the same column order and of type table 98h.

I want to return distinct syms based on newest data in the order date --> time --> Capture.

Therefore, this table should return:

time              date         sym         Capture
14:00:00.000      2022.09.12   `AAPL       2022.09.12D14:20:00.000000000
0Nt               2022.09.11   `MSFT       2022.09.11D15:00:00.000000000

Thanks for the help!

q kdb
2 Answers
cols[table] xcols 0!select by sym from `date`time`Capture xasc table
rianoc0

Fby could also do it:

q)t:([]time:12:00:00.000 10:00:00.000 14:00:00.000 0N 16:00:00.000 0N 0N;date:2022.09.12 2022.09.10 2022.09.12 2022.09.11 2022.09.11 2022.09.11 2022.09.11;sym:`AAPL`MSFT`AAPL`AAPL`AAPL`MSFT`MSFT;Capture:2022.09.12D15:30:00.0 2022.09.10D11:20:00.0 2022.09.12D14:20:00.0 2022.09.11D10:05:00.0 2022.09.12D17:20:00.0 2022.09.11D11:30:00.0 2022.09.11D15:00:00.0);

q)select from t where({x=max x};Capture^date+time)fby sym
time         date       sym  Capture
----------------------------------------------------------
14:00:00.000 2022.09.12 AAPL 2022.09.12D14:20:00.000000000
             2022.09.11 MSFT 2022.09.11D15:00:00.000000000
terrylynch0
Related