cancel
Showing results for 
Search instead for 
Did you mean: 

fby with variable grouped columns

lui_voleon
New Contributor
if i want to wrap a fby select statement in to a function with a variable grp key
let's say i have a select queries with fby on 3 columns, `a`b`c

select from tbl where time(max;time) fby ([] a ; b ; c)

now if i want to generize to group by a variable of column
let's say grp:`a`b`c, pass into a function

f:{[tbl ; grp]
 select from tbl where time=(max;time) fby ...
}

how should I do it
3 REPLIES 3

JFSawyer88
New Contributor
A functional select can achieve this. Something like:

wClause:enlist(=;`time;(fby;(enlist;max;`time);(flip;(!;enlist grp;enlist,grp))))
?[tbl;wClause;0b;()]

This starts with a dictionary, flips it into the desired table, and then applies they fby as usual.

 

alvi_kabir919
New Contributor
q)show t:([]time:asc 4?.z.T;a:1 2 1 2;b:1 2 1 2;c:1 2 1 2;d:4?100)
time         a b c d
---------------------
02:59:16.636 1 1 1 66
07:14:12.294 2 2 2 36
08:31:52.958 1 1 1 37
10:50:05.304 2 2 2 44

q)select from t where time=(max;time) fby ([]a;b;c)
time         a b c d
---------------------
08:31:52.958 1 1 1 37
10:50:05.304 2 2 2 44

q){[tbl;grp] ?[tbl;enlist(=;`time;(fby;(enlist;max;`time);(flip;(!;enlist grp;enlist,grp))));0b;()]}[t;`a`b`c]
time         a b c d
---------------------
08:31:52.958 1 1 1 37
10:50:05.304 2 2 2 44

Tip: Use 'parse' to inspect the functional form of select statements and tinker with them to your liking 🙂

i like to avoid a functional select if possible

q){[tbl;grp] select from t where time=(max;time) fby grp#0!t}[t;`a`b`c]
time         a b c d
---------------------
05:50:33.545 1 1 1 1
09:36:00.090 2 2 2 90

Cheers,
   Attila