minSimpleState

[Edit this page](https://github.com/ClickHouse/clickhouse-docs/blob/main/docs/guides/examples/aggregate_function_combinators/minSimpleState.md)

## Description[​](#description "Direct link to Description")

The [`SimpleState`](/docs/sql-reference/aggregate-functions/combinators#-simplestate) combinator can be applied to the [`min`](/docs/sql-reference/aggregate-functions/reference/min)
function to return the minimum value across all input values. It returns the
result with type [`SimpleAggregateFunction`](/docs/sql-reference/data-types/simpleaggregatefunction).

## Example usage[​](#example-usage "Direct link to Example usage")

Let's look at a practical example using a table that tracks daily temperature
readings. For each location, we want to maintain the lowest temperature recorded.
Using the `SimpleAggregateFunction` type with `min` automatically updates the
stored value when a lower temperature is encountered.

Create the source table for raw temperature readings:

```
CREATE TABLE raw_temperature_readings
(
    location_id UInt32,
    location_name String,
    temperature Int32,
    recorded_at DateTime DEFAULT now()
)
    ENGINE = MergeTree()
ORDER BY (location_id, recorded_at);
```

Create the aggregate table that will store the min temperatures:

```
CREATE TABLE temperature_extremes
(
    location_id UInt32,
    location_name String,
    min_temp SimpleAggregateFunction(min, Int32),  -- Stores minimum temperature
    max_temp SimpleAggregateFunction(max, Int32)   -- Stores maximum temperature
)
ENGINE = AggregatingMergeTree()
ORDER BY location_id;
```

Create an Incremental materialized view that will act as an insert trigger
for inserted data and maintains the minimum, maximum temperatures per location.

```
CREATE MATERIALIZED VIEW temperature_extremes_mv
TO temperature_extremes
AS SELECT
    location_id,
    location_name,
    minSimpleState(temperature) AS min_temp,     -- Using SimpleState combinator
    maxSimpleState(temperature) AS max_temp      -- Using SimpleState combinator
FROM raw_temperature_readings
GROUP BY location_id, location_name;
```

Insert some initial temperature readings:

```
INSERT INTO raw_temperature_readings (location_id, location_name, temperature) VALUES
(1, 'North', 5),
(2, 'South', 15),
(3, 'West', 10),
(4, 'East', 8);
```

These readings are automatically processed by the materialized view. Let's check
the current state:

```
SELECT
    location_id,
    location_name,
    min_temp,     -- Directly accessing the SimpleAggregateFunction values
    max_temp      -- No need for finalization function with SimpleAggregateFunction
FROM temperature_extremes
ORDER BY location_id;
```

```
┌─location_id─┬─location_name─┬─min_temp─┬─max_temp─┐
│           1 │ North         │        5 │        5 │
│           2 │ South         │       15 │       15 │
│           3 │ West          │       10 │       10 │
│           4 │ East          │        8 │        8 │
└─────────────┴───────────────┴──────────┴──────────┘
```

Insert some more data:

```
INSERT INTO raw_temperature_readings (location_id, location_name, temperature) VALUES
    (1, 'North', 3),
    (2, 'South', 18),
    (3, 'West', 10),
    (1, 'North', 8),
    (4, 'East', 2);
```

View the updated extremes after new data:

```
SELECT
    location_id,
    location_name,
    min_temp,  
    max_temp
FROM temperature_extremes
ORDER BY location_id;
```

```
┌─location_id─┬─location_name─┬─min_temp─┬─max_temp─┐
│           1 │ North         │        3 │        8 │
│           1 │ North         │        5 │        5 │
│           2 │ South         │       18 │       18 │
│           2 │ South         │       15 │       15 │
│           3 │ West          │       10 │       10 │
│           3 │ West          │       10 │       10 │
│           4 │ East          │        2 │        2 │
│           4 │ East          │        8 │        8 │
└─────────────┴───────────────┴──────────┴──────────┘
```

Notice above that we have two inserted values for each location. This is because
parts haven't yet been merged (and aggregated by `AggregatingMergeTree`). To get
the final result from the partial states we need to add a `GROUP BY`:

```
SELECT
    location_id,
    location_name,
    min(min_temp) AS min_temp,  -- Aggregate across all parts 
    max(max_temp) AS max_temp   -- Aggregate across all parts
FROM temperature_extremes
GROUP BY location_id, location_name
ORDER BY location_id;
```

We now get the expected result:

```
┌─location_id─┬─location_name─┬─min_temp─┬─max_temp─┐
│           1 │ North         │        3 │        8 │
│           2 │ South         │       15 │       18 │
│           3 │ West          │       10 │       10 │
│           4 │ East          │        2 │        8 │
└─────────────┴───────────────┴──────────┴──────────┘
```

Note

With `SimpleState`, you don't need to use the `Merge` combinator to combine
partial aggregation states.

## See also[​](#see-also "Direct link to See also")

- [`min`](/docs/sql-reference/aggregate-functions/reference/min)
- [`SimpleState combinator`](/docs/sql-reference/aggregate-functions/combinators#-simplestate)
- [`SimpleAggregateFunction type`](/docs/sql-reference/data-types/simpleaggregatefunction)

- [Description](#description)- [Example usage](#example-usage)- [See also](#see-also)

Was this page helpful?