into_values()

Returns the lowest values and associated data from a MinNBy aggregate

Since 1.16.0

This returns the smallest values seen by the aggregate and the corresponding values associated with them. Note that Postgres requires an input argument with type matching the associated value in order to determine the response type.

Samples

Find the bottom 5 values from i * 13 % 10007 for i = 1 to 10000, and the integer result of the operation that generated that modulus.

SELECT into_values(
    min_n_by(sub.mod, sub.div, 5),
    NULL::INT)
FROM (
  SELECT (i * 13) % 10007 AS mod, (i * 13) / 10007 AS div
  FROM generate_series(1,10000) as i
) sub;

Output:

into_values
-------------
(1,9)
(2,5)
(3,1)
(4,10)
(5,6)

Arguments

The syntax is:

into_values (
    agg MinNBy
) SETOF BIGINT | SETOF DOUBLE PRECISION | SETOF TIMESTAMPTZ

Name	Type	Default	Required	Description
agg	MinNBy	-	✔	The aggregate to return the results from. Note that the exact type here varies based on the type of data stored.
dummy	ANYELEMENT	-	✔	This is purely to inform Postgres of the response type. A NULL cast to the appropriate type is typical.

Returns

Column	Type	Description
into_values	SETOF BIGINT | SETOF DOUBLE PRECISION | SETOF TIMESTAMPTZ	The lowest values seen while creating this aggregate.