CODEWITHSUNDEEP
kdb+
Utsav
Utsav

Reputation: 5918

how to remove redundant use of last keyword in qsql

I have a table:

q)t:([] sym:`AAPL`MSFT`AMZN`AAPL`MSFT`AMZN; px:1 2 3 11 22 33; sh:100 200 300 1000 2000 3000)

I want to get the last px and sh by sym which can be obtained using last function two times:

q)select last px, last sh by sym from t
sym | px sh
----| -------
AAPL| 11 1000
AMZN| 33 3000
MSFT| 22 2000

How can we use last keyword only once to get above output?(Because in practical cases sometimes we need to use last on more than 30 columns)

My Failed attempts:

q)select last (px;sh) by sym from t
q)select {last x}@'(px;sh) by sym from t

Upvotes: 0

Views: 106

Answers (2)

terrylynch
terrylynch

Reputation: 13572

A common approach to the problem is to use fby which allows you to apply a function such as first or last (or a lambda) across all columns:

t:([]c1:10?`A`B`C;c2:10?10;c3:10?"xyz";c4:.z.D-til 10)

q)select from t where i=(last;i)fby c1
c1 c2 c3 c4
-------------------
A  9  z  2019.10.01
C  7  y  2019.09.29
B  0  x  2019.09.28

q)select from t where i=({first x};i)fby c1
c1 c2 c3 c4
-------------------
B  6  x  2019.10.07
C  6  x  2019.10.06
A  4  y  2019.10.02

To answer your question in a comment regarding applying a different function per column, you would have to use a functional select such as:

q)?[t;();{x!x}1#`c1;{x!((first;last)x in `c2`c4),'x}cols[t]except`c1]
c1| c2 c3 c4
--| ----------------
A | 9  y  2019.10.01
B | 0  x  2019.09.28
C | 7  x  2019.09.29

This uses last for columns c2 and c4, then uses first for the other columns

Upvotes: 2

Michael McParland
Michael McParland

Reputation: 906

For cases like this, using a by phrase together with no selection of columns is the same as applying last to all columns.

select by sym from t

Should do the trick

q)(select last px, last sh by sym from t)~select by sym from t
1b

Upvotes: 6

Related Questions