fby: Find the Highest Price for Each Symbol Using fby

Question

Given the following table of market prices:

t:([] sym:`AAPL`AAPL`AAPL`MSFT`MSFT`GOOG`GOOG;
      time:09:30:00 10:00:00 10:30:00 09:30:00 10:00:00 09:30:00 10:00:00;
      price:185.2 187.5 186.8 410.1 412.3 142.8 141.5)

Result:

sym  time     price
-------------------
AAPL 09:30:00 185.2
AAPL 10:00:00 187.5
AAPL 10:30:00 186.8
MSFT 09:30:00 410.1
MSFT 10:00:00 412.3
GOOG 09:30:00 142.8
GOOG 10:00:00 141.5

Select all rows where the price is the maximum price observed for that symbol.

Expected result:

sym  time     price
-------------------
AAPL 10:00:00 187.5
MSFT 10:00:00 412.3
GOOG 09:30:00 142.8

Solution

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

Explanation

The goal is to keep only the rows whose price equals the maximum price for their symbol.

For example:

AAPL -> max price = 187.5
MSFT -> max price = 412.3
GOOG -> max price = 142.8

The expression:

(max;price) fby sym

means:

Apply max to the price column for each sym group,
then broadcast the result back to every row in that group.

The result is a vector with the same length as the table:

187.5 187.5 187.5 412.3 412.3 142.8 142.8

corresponding to:

sym  price maxPrice
-------------------
AAPL 185.2 187.5
AAPL 187.5 187.5
AAPL 186.8 187.5
MSFT 410.1 412.3
MSFT 412.3 412.3
GOOG 142.8 142.8
GOOG 141.5 142.8

The where clause then compares each row's price against its group's maximum:

price=(max;price) fby sym

Result:

0100110b

Finally:

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

returns only the rows where the comparison is true.

Why Use fby?

Without fby, a common approach would be:

select max price by sym from t

However, that only returns the maximum prices:

sym | price
----| -----
AAPL| 187.5
GOOG| 142.8
MSFT| 412.3

and loses the other columns such as time.

The fby operator allows you to calculate a group-level statistic while retaining the original rows, making it ideal for filtering records based on group aggregates.

Handling Ties

If multiple rows share the maximum price for a symbol:

AAPL 10:00:00 187.5
AAPL 10:30:00 187.5

both rows will be returned.

This makes the solution naturally handle ties without any additional logic.

Summary

The expression:

(max;price) fby sym

computes the maximum price for each symbol and broadcasts the result back to every row in that symbol's group.

Combining it with:

where price=(max;price) fby sym

provides a concise way to select the rows corresponding to the highest price for each symbol while retaining all columns in the original table.