kdb+

How to select distinct by max value of other column

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?

Solution

  • 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