Skip to main content
Get the N largest values from a column, with an associated piece of data per value. For example, you can return an accompanying column, or the full row. The max_n_by() functions give the same results as the regular SQL query SELECT ... ORDER BY ... LIMIT n. But unlike the SQL query, they can be composed and combined like other aggregate hyperfunctions. To get the N smallest values with accompanying data, use min_n_by(). To get the N largest values without accompanying data, use max_n(). This function group uses the two-step aggregation pattern. In addition to the usual aggregate function max_n_by, it also includes accessors and rollup functions.

​
Two-step aggregation

This group of functions uses the two-step aggregation pattern. Rather than calculating the final result in one step, you first create an intermediate aggregate by using the aggregate function. Then, use any of the accessors on the intermediate aggregate to calculate a final result. You can also roll up multiple intermediate aggregates with the rollup functions. The two-step aggregation pattern has several advantages:
  1. More efficient because multiple accessors can reuse the same aggregate
  2. Easier to reason about performance, because aggregation is separate from final computation
  3. Easier to understand when calculations can be rolled up into larger intervals, especially in window functions and continuous aggregates
  4. Perform retrospective analysis even when underlying data is dropped, because the intermediate aggregate stores extra information not available in the final result
To learn more, see the blog post on two-step aggregates.

​
Samples

This example assumes that you have a table of stock trades in this format: 
CREATE TABLE stock_sales(
    ts TIMESTAMPTZ,
    symbol TEXT,
    price FLOAT,
    volume INT
);
Find the 10 largest transactions in the table, what time they occurred, and what symbol was being traded: 
SELECT
    (data).ts,
    (data).symbol,
    value AS transaction
FROM
    into_values((
        SELECT max_n_by(price * volume, stock_sales, 10)
        FROM stock_sales
    ),
    NULL::stock_sales);

​
Available functions

​
Aggregate

  • max_n_by(): construct an aggregate that keeps track of the largest values and associated data

​
Accessors

  • into_values(): return the N highest values with their associated data

​
Rollup

  • rollup(): combine multiple MaxNBy aggregates