# max_n() 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.

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

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

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

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

## Returns

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

`max_n` | `MaxN` | The 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

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

`agg` | `MaxN` | The aggregate to return the results from. Note that the exact type here varies based on the type of data stored in the aggregate. |

## Returns

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

`into_array` | `BIGINT[]` , `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 valFROM 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

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

`agg` | `MaxN` | The aggregate to return the results from. Note that the exact type here varies based on the type of data stored. |

## Returns

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

`into_values` | `SETOF BIGINT` , `SETOF DOUBLE PRECISION` , `SETOF 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_values(max_n(sub.val, 5))FROM (SELECT (i * 13) % 10007 AS valFROM generate_series(1,10000) as i) sub;

into_values-------------1000610005100041000310002

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

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

`agg` | `MaxN` | The aggregates being combined |

## Returns

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

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

You can query for the 10 largest transactions each day:

WITH t as (SELECTtime_bucket('1 day'::interval, ts) as day,max_n(price * volume, 10) AS daily_maxFROM stock_salesGROUP BY time_bucket('1 day'::interval, ts))SELECTday, as_array(daily_max)FROM t;

Keywords

Found an issue on this page?

Report an issue!