Introduction

Get the N largest values from a column.

The max_n() 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, use min_n(). To get the N largest values with accompanying data, use max_n_by().

Related hyperfunction groups

Aggregate

max_n
Find the largest values in a set of data

Accessor

into_array
Returns an array of the highest values from a MaxN aggregate
into_values
Returns the highest values from a MaxN aggregate

Rollup

rollup
Combine multiple MaxN aggregates
max_n(
value BIGINT | DOUBLE PRECISION | TIMESTAMPTZ,
capacity BIGINT
) MaxN

Construct an aggregate which will keep track of the largest values passed through it.

Required arguments
NameTypeDescription
valueBIGINT, DOUBLE PRECISION, TIMESTAMPTZThe values passed into the aggregate
capacityBIGINTThe number of values to retain.
Returns
ColumnTypeDescription
max_nMaxNThe compiled aggregate. Note that the exact type will be MaxInts, MaxFloats, or MaxTimes depending on the input type
into_array (
agg MaxN
) BIGINT[] | DOUBLE PRECISION[] | TIMESTAMPTZ[]

Return the N largest values seen by the aggregate. The values are formatted as an array in decreasing order.

Required arguments
NameTypeDescription
aggMaxNThe aggregate to return the results from. Note that the exact type here varies based on the type of data stored in the aggregate.
Returns
ColumnTypeDescription
into_arrayBIGINT[], DOUBLE PRECISION[], TIMESTAMPTZ[]The largest values seen while creating this aggregate.
Examples

Find the top 5 values from i * 13 % 10007 for i = 1 to 10000:

SELECT into_array(
max_n(sub.val, 5))
FROM (
SELECT (i * 13) % 10007 AS val
FROM generate_series(1,10000) as i
) sub;
into_array
---------------------------------
{10006,10005,10004,10003,10002}
into_values (
agg MaxN
) SETOF BIGINT | SETOF DOUBLE PRECISION | SETOF TIMESTAMPTZ

Return the N largest values seen by the aggregate.

Required arguments
NameTypeDescription
aggMaxNThe aggregate to return the results from. Note that the exact type here varies based on the type of data stored.
Returns
ColumnTypeDescription
into_valuesSETOF BIGINT, SETOF DOUBLE PRECISION, SETOF TIMESTAMPTZThe largest values seen while creating this aggregate.
Examples

Find the top 5 values from i * 13 % 10007 for i = 1 to 10000:

SELECT into_values(
max_n(sub.val, 5))
FROM (
SELECT (i * 13) % 10007 AS val
FROM generate_series(1,10000) as i
) sub;
into_values
-------------
10006
10005
10004
10003
10002
rollup(
agg MaxN
) MaxN

This aggregate combines the aggregates generated by other max_n aggregates. Combined with an accessor, it returns the maximum values found across all the aggregated data.

Required arguments
NameTypeDescription
aggMaxNThe aggregates being combined
Returns
ColumnTypeDescription
rollupMaxNAn aggregate over all of the contributing values.

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
);

You can query for the 10 largest transactions each day:

WITH t as (
SELECT
time_bucket('1 day'::interval, ts) as day,
max_n(price * volume, 10) AS daily_max
FROM stock_sales
GROUP BY time_bucket('1 day'::interval, ts)
)
SELECT
day, as_array(daily_max)
FROM t;

Keywords

Found an issue on this page?Report an issue or Edit this page in GitHub.