Introduction

Get the N smallest values from a column.

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

Related hyperfunction groups

Aggregate

min_n
Find the smallest values in a set of data

Accessor

into_array
Returns an array of the lowest values from a MinN aggregate
into_values
Returns the lowest values from a MinN aggregate

Rollup

rollup
Combine multiple MinN aggregates
min_n(
value BIGINT | DOUBLE PRECISION | TIMESTAMPTZ,
capacity BIGINT
) MinN

Construct an aggregate that keeps track of the smallest values passed through it.

Required arguments
NameTypeDescription
valueBIGINT, DOUBLE PRECISION, TIMESTAMPTZThe values passed into the aggregate
capacityBIGINTThe number of values to retain.
Returns
ColumnTypeDescription
min_nMinNThe compiled aggregate. Note that the exact type is MinInts, MinFloats, or MinTimes depending on the input type
into_array (
agg MinN
) BIGINT[] | DOUBLE PRECISION[] | TIMESTAMPTZ[]

Returns the N lowest values seen by the aggregate. The values are formatted as an array in increasing order.

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

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

SELECT into_array(
min_n(sub.val, 5))
FROM (
SELECT (i * 13) % 10007 AS val
FROM generate_series(1,10000) as i
) sub;
into_array
---------------------------------
{1,2,3,4,5}
into_values (
agg MinN
) SETOF BIGINT | SETOF DOUBLE PRECISION | SETOF TIMESTAMPTZ

Return the N lowest values seen by the aggregate.

Required arguments
NameTypeDescription
aggMinNThe 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 lowest values seen while creating this aggregate.
Examples

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

SELECT toolkit_experimental.into_array(
toolkit_experimental.min_n(sub.val, 5))
FROM (
SELECT (i * 13) % 10007 AS val
FROM generate_series(1,10000) as i
) sub;
into_values
---------------------------------
1
2
3
4
5
rollup(
agg MinN
) MinN

This aggregate combines the aggregates generated by other min_n aggregates and returns the minimum values found across all the aggregated data.

Required arguments
NameTypeDescription
aggMinNThe aggregates being combined
Returns
ColumnTypeDescription
rollupMinNAn 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 smallest transactions each day:

WITH t as (
SELECT
time_bucket('1 day'::interval, ts) as day,
min_n(price * volume, 10) AS daily_min
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!