Search code examples
kdb

How to convert this nested query into a single by query (simulating a MySql window function)?

Given the following

\S 1234
t:([]a:10 ? til 5; b:10 ? til 100; c:10 ? til 10000);

select a,b,c from ij[t; select maxc:max c by a from t] where c=maxc;

Instead of joining an inner query to use as part of the filter, is it possible to have q select the first record in each (window grouped by a and sorted by c descending)? If so, no join would be necessary.

The result would be the same. I could then do testing to see if performance if different. But mainly just trying to understand what kinds of queries are and are not possible.

Solution

  • You could try fby which matches the result of your current query

    q)select from t where c = (max;c) fby a
a b  c
---------
2 2  5480
3 39 9473
4 77 6458

    https://code.kx.com/q/ref/fby/