# max_n_by() functions

ToolkitTimescaleDB Toolkit functions are available under Timescale Community Edition. They are automatically included with Timescale, but must be installed separately for self-hosted TimescaleDB. Click to learn more.## Introduction

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()`

.

###### Related hyperfunction groups

### Aggregate

- max_n_by
- Track the largest values and associated data in a set of values

### Accessor

- into_values
- Returns the highest values and associated data from a MaxNBy aggregate

### Rollup

- rollup
- Combine multiple MaxNBy aggregates

max_n_by(value BIGINT | DOUBLE PRECISION | TIMESTAMPTZ,data ANYELEMENT,capacity BIGINT) MaxNBy

Construct an aggregate that keeps track of the largest values passed through it, as well as some associated data which is passed alongside the value.

## Required arguments

Name | Type | Description |
---|---|---|

`value` | `BIGINT` , `DOUBLE PRECISION` , `TIMESTAMPTZ` | The values passed into the aggregate |

`data` | `ANYELEMENT` | The data associated with a particular value |

`capacity` | `BIGINT` | The number of values to retain. |

## Returns

Column | Type | Description |
---|---|---|

`max_n_by` | `MaxNBy` | The compiled aggregate. Note that the exact type will be MaxByInts, MaxByFloats, or MaxByTimes depending on the input type |

into_values(agg MaxNBy,dummy ANYELEMENT) TABLE (value BIGINT | DOUBLE PRECISION | TIMESTAMPTZ,data ANYELEMENT)

This will return the largest values seen by the aggregate and the corresponding values associated with them. Note that PostgresQL requires an input argument with type matching the associated value in order to deterimine the response type.

## Required arguments

Name | Type | Description |
---|---|---|

`agg` | `MaxNBy` | 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 PostgresQL of the response type. A NULL cast to the appropriate type is typical. |

## Returns

Column | Type | Description |
---|---|---|

`into_values` | `TABLE (value BIGINT` , `DOUBLE PRECISION` , `TIMESTAMPTZ, data ANYELEMENT)` | The largest values and associated data seen while creating this aggregate. |

## Examples

Find the top 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(max_n_by(sub.mod, sub.div, 5),NULL::INT)FROM (SELECT (i * 13) % 10007 AS mod, (i * 13) / 10007 AS divFROM generate_series(1,10000) as i) sub;

into_values-------------(10006,3)(10005,7)(10004,11)(10003,2)(10002,6)

rollup(agg MaxNBy) MaxNBy

This aggregate combines the aggregates generated by other max*n*by
aggregates and returns the maximum values, with associated data, found
across all the aggregated data.

## Required arguments

Name | Type | Description |
---|---|---|

`agg` | `MaxNBy` | The aggregates being combined |

## Returns

Column | Type | Description |
---|---|---|

`rollup` | `MaxNBy` | An 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);

Find the 10 largest transactions in the table, what time they occurred, and what symbol was being traded:

SELECT(data).time,(data).symbol,value AS transactionFROMinto_values((SELECT max_n_by(price * volume, stock_sales, 10)FROM stock_sales),NULL::stock_sales);

Keywords

Found an issue on this page?

Report an issue!