I have the following code:
select count i by sym, typ from tbl;
Which returns the following:
| sym | typ | x |
-------------------
| ABC | A | 122 |
| ABC | B | 37 |
| JKL | C | 19 |
| XYZ | A | 3 |
| XYZ | B | 206 |
| XYZ | C | 81 |
...
What I would like to do now is apply some sort of distinct
or first
or both that would result in only one of each sym
being chosen, with the typ
column having the max count value from x
. That is:
| sym | typ | x |
-------------------
| ABC | A | 122 |
| JKL | C | 19 |
| XYZ | B | 206 |
...
How would I go about achieving this?
Use fby
(https://code.kx.com/q/ref/fby):
q)tbl:([]sym:`ABC`ABC`JKL`XYZ`XYZ`XYZ;typ:`A`B`C`A`B`C;x:122 37 19 3 206 81)
q)select from tbl where x=(max;x)fby sym
sym typ x
-----------
ABC A 122
JKL C 19
XYZ B 206