Select the Last N Rows per Symbol

Question

Given a table t with a sym column, select the last 3 rows for each symbol.

For example:

t:([] sym:`a`b`a`b`a`a`b`a; p:til 8)

Solution

select from t where ({[x] x in -3#x};i) fby sym

Alternative Solution

select from t where i in ({(count x)#-3 sublist x};i) fby sym

Alternative Solution

select from t where i>=({first -3#x};i) fby sym

Explanation

fby runs a function per sym group and broadcasts the result back to every row of that group — here the function is applied to i, the row index, rather than to a price or quantity column.

{[x] x in -3#x}

takes a group's list of row indexes x, finds the last 3 of them (-3#x), and checks, for every index in the group, whether it's one of those last 3. The result is a boolean per row, true only for a symbol's final 3 rows — exactly the mask where needs.

The other two alternatives compute the same boolean differently. The first returns, for each row, the group's last 3 indexes (repeated with # so the result is as long as the group, as fby requires), and i in tests membership. The last compares each row's index against the first of the last 3 ({first -3#x}) using >=: indexes within a group are always increasing, so any index at or beyond that cutoff is one of the last 3.