Select the Maximum-Price Row per Symbol

Question

Given a table t with columns sym and price, select all rows from t where price is the maximum price for each symbol.

For example:

t:([] sym:`A`B`A`B`A; price:10 20 30 20 30f)

Solution

select from t where price=(max;price) fby sym

Explanation

fby (filter-by) computes an aggregation per group, but — unlike a plain by query — broadcasts the result back out to every row in that group, rather than collapsing the table down to one row per group.

(max;price) fby sym

computes the maximum price within each sym group, and assigns that group's maximum back onto every row belonging to that group. So a 5-row table with 2 symbols produces 5 values, not 2 — each row sees its own symbol's maximum price.

The where clause then keeps only the rows where the row's own price equals its group's maximum — i.e. the row(s) that actually hit the high for their symbol. Because this is a row-level comparison rather than a by-grouped aggregation, it naturally returns all rows that tie for the max within a symbol, not just one.