> ## Documentation Index
> Fetch the complete documentation index at: https://mintlify-poc.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# Minimum and maximum overview

> Find the smallest and largest values in a dataset

Find the smallest and largest values in a dataset. These specialized hyperfunctions make
it easier to write queries that identify extreme values in your data.

They help you answer questions such as:

* What are the N smallest or largest values in my dataset?
* Which rows contain the minimum or maximum values?
* How can I efficiently track top/bottom values over time?

This function family provides four related function groups:

* [`min_n()`][min_n]: Get the N smallest values from a column
* [`max_n()`][max_n]: Get the N largest values from a column
* [`min_n_by()`][min_n_by]: Get the N smallest values with accompanying data (like full rows)
* [`max_n_by()`][max_n_by]: Get the N largest values with accompanying data (like full rows)

These function groups use the [two-step aggregation][two-step-aggregation]
pattern. Each group includes an aggregate function to create intermediate aggregates,
accessor functions to extract results, and rollup functions to combine aggregates.

The minimum and maximum 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.

## Two-step aggregation

This group of functions uses the two-step aggregation pattern.

Rather than calculating the final result in one step, you first create an
intermediate aggregate by using the aggregate function.

Then, use any of the accessors on the intermediate aggregate to calculate a
final result. You can also roll up multiple intermediate aggregates with the
rollup functions.

The two-step aggregation pattern has several advantages:

1. More efficient because multiple accessors can reuse the same aggregate
2. Easier to reason about performance, because aggregation is separate from
   final computation
3. Easier to understand when calculations can be rolled up into larger
   intervals, especially in window functions and continuous aggregates
4. Perform retrospective analysis even when underlying data is dropped, because
   the intermediate aggregate stores extra information not available in the
   final result

To learn more, see the [blog post on two-step aggregates][blog-two-step-aggregates].

[blog-two-step-aggregates]: https://www.timescale.com/blog/how-postgresql-aggregation-works-and-how-it-inspired-our-hyperfunctions-design

## Samples

### Find the smallest values

Get the 5 smallest values from a calculation. This example uses `min_n()` to
find the bottom 5 values from `i * 13 % 10007` for i = 1 to 10000:

```sql theme={"dark"}
SELECT into_array(
    min_n(sub.val, 5))
FROM (
  SELECT (i * 13) % 10007 AS val
  FROM generate_series(1,10000) as i
) sub;
```

Output:

```sql theme={"dark"}
into_array
---------------------------------
{1,2,3,4,5}
```

### Find the largest values

Get the 5 largest values from a calculation. This example uses `max_n()` to
find the top 5 values from `i * 13 % 10007` for i = 1 to 10000:

```sql theme={"dark"}
SELECT into_array(
    max_n(sub.val, 5))
FROM (
  SELECT (i * 13) % 10007 AS val
  FROM generate_series(1,10000) as i
) sub;
```

Output:

```sql theme={"dark"}
into_array
---------------------------------
{10006,10005,10004,10003,10002}
```

### Find the smallest transactions with details

This example assumes you have a table of stock trades:

```sql theme={"dark"}
CREATE TABLE stock_sales(
    ts TIMESTAMPTZ,
    symbol TEXT,
    price FLOAT,
    volume INT
);
```

Find the 10 smallest transactions each day with their timestamps and symbols.
This example uses `min_n_by()` to track both the transaction size and
associated row data:

```sql theme={"dark"}
WITH daily_min AS (
    SELECT
        time_bucket('1 day'::interval, ts) as day,
        min_n_by(price * volume, stock_sales, 10) AS min_transactions
    FROM stock_sales
    GROUP BY day
)
SELECT
    day,
    (data).ts,
    (data).symbol,
    value AS transaction_size
FROM daily_min,
     LATERAL into_values(min_transactions, NULL::stock_sales);
```

### Find the largest transactions with details

Find the 10 largest transactions each day. This example uses `max_n_by()`:

```sql theme={"dark"}
WITH daily_max AS (
    SELECT
        time_bucket('1 day'::interval, ts) as day,
        max_n_by(price * volume, stock_sales, 10) AS max_transactions
    FROM stock_sales
    GROUP BY day
)
SELECT
    day,
    (data).ts,
    (data).symbol,
    value AS transaction_size
FROM daily_max,
     LATERAL into_values(max_transactions, NULL::stock_sales);
```

## Available functions

### Minimum values

* [`min_n()`][min_n]: get the N smallest values from a column

### Maximum values

* [`max_n()`][max_n]: get the N largest values from a column

### Minimum values with data

* [`min_n_by()`][min_n_by]: get the N smallest values with accompanying data

### Maximum values with data

* [`max_n_by()`][max_n_by]: get the N largest values with accompanying data

[max_n]: /api-reference/timescaledb-toolkit/minimum-and-maximum/max_n

[max_n_by]: /api-reference/timescaledb-toolkit/minimum-and-maximum/max_n_by

[min_n]: /api-reference/timescaledb-toolkit/minimum-and-maximum/min_n

[min_n_by]: /api-reference/timescaledb-toolkit/minimum-and-maximum/min_n_by

[two-step-aggregation]: #two-step-aggregation