MIN

Aggregate · Window

NULLs are ignored; all-NULL groups and frames return NULL.

Syntax

A window function keeps each input row and adds a result for that row. PARTITION BY groups rows with matching values; without it, all input rows form one group.

Aggregate

min([DISTINCT] value)

Window

min(value) OVER ([PARTITION BY key] [ORDER BY ordering_key] [ROWS frame])

RANGE frames with a numeric or time offset are not supported. Use unique ORDER BY values for calculations that advance one row at a time. DISTINCT is not supported in these window forms.

Arguments

Aggregate and window

Name	Type	Required	Description
value	BOOLEAN, TINYINT, SMALLINT, INTEGER, BIGINT, FLOAT, DOUBLE, DECIMAL(p, s), STRING, BINARY, DATE, TIME, TIMESTAMP, ARRAY or STRUCT	Yes	Value to compare. Numbers use numeric order, text uses the string sort order, and dates/timestamps use chronological order. ARRAY and STRUCT comparison requires recursively comparable elements/fields; MAP values are not supported across these aggregate and window forms. For DECIMAL(p, s), p is precision (total digits) and s is scale (digits after the decimal point).

Returns

Type: Same SQL type as value
NULL behavior: NULLs are ignored; all-NULL groups and frames return NULL.
Empty input: Returns NULL on an empty global aggregate or empty frame.

Properties

Property	Value
Deterministic	Yes
Supports DISTINCT	Partial (Grouped aggregation only; see the window limitations below.)
Supports window usage	Yes
Supports window frames	Partial (Use the ROWS forms shown here; RANGE offsets are not supported.)
Exact result	Yes

Usage notes

INTEGER and INT mean the same type; STRING means VARCHAR text. NULL can be used with a supported type. Cast NULL explicitly when its type would otherwise be unclear.
Query tools may report a DECIMAL result as DOUBLE or a TIME result as INTEGER. The returned value still keeps its decimal precision and scale, or its time type. A reported type does not convert the value.
Use DISTINCT in grouped aggregation only. It does not provide duplicate removal in a window call.

Examples

Sample data

id	k	v
1	a	2
2	a	2
3	a	4
4	a	NULL
5	b	6
6	b	8
7	c	NULL

Integer values

WITH t("value") AS (VALUES (3), (7), (1), (9), (2)) SELECT min("value") AS result FROM t;

result
1

Ignore NULLs

WITH t("value") AS (VALUES (3), (CAST(NULL AS INTEGER)), (7), (1)) SELECT min("value") AS result FROM t;

result
1

Aggregate the sample values

WITH sample(id, k, v) AS (
  VALUES (1, 'a', 2), (2, 'a', 2), (3, 'a', 4),
         (4, 'a', CAST(NULL AS INT)), (5, 'b', 6), (6, 'b', 8),
         (7, 'c', CAST(NULL AS INT))
)
SELECT min(v) AS result FROM sample;

result
2

One result per group, including an all-NULL group

WITH sample(id, k, v) AS (
  VALUES (1, 'a', 2), (2, 'a', 2), (3, 'a', 4),
         (4, 'a', CAST(NULL AS INT)), (5, 'b', 6), (6, 'b', 8),
         (7, 'c', CAST(NULL AS INT))
)
SELECT k, min(v) AS result
FROM sample
GROUP BY k
ORDER BY k NULLS LAST;

k	result
a	2
b	6
c	NULL

DISTINCT removes repeated input values

WITH sample(id, k, v) AS (
  VALUES (1, 'a', 2), (2, 'a', 2), (3, 'a', 4),
         (4, 'a', CAST(NULL AS INT)), (5, 'b', 6), (6, 'b', 8),
         (7, 'c', CAST(NULL AS INT))
)
SELECT min(DISTINCT v) AS result FROM sample;

result
2

Window over all rows in each group

WITH sample(id, k, v) AS (
  VALUES (1, 'a', 2), (2, 'a', 2), (3, 'a', 4),
         (4, 'a', CAST(NULL AS INT)), (5, 'b', 6), (6, 'b', 8),
         (7, 'c', CAST(NULL AS INT))
)
SELECT id, k, min(v) OVER (PARTITION BY k) AS result
FROM sample
ORDER BY id NULLS LAST;

id	k	result
1	a	2
2	a	2
3	a	2
4	a	2
5	b	6
6	b	6
7	c	NULL

Running window

WITH sample(id, k, v) AS (
  VALUES (1, 'a', 2), (2, 'a', 2), (3, 'a', 4),
         (4, 'a', CAST(NULL AS INT)), (5, 'b', 6), (6, 'b', 8),
         (7, 'c', CAST(NULL AS INT))
)
SELECT id, k, min(v) OVER (PARTITION BY k ORDER BY id NULLS LAST ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS result
FROM sample
ORDER BY id NULLS LAST;

id	k	result
1	a	2
2	a	2
3	a	2
4	a	2
5	b	6
6	b	6
7	c	NULL

Sliding window with one preceding row

WITH sample(id, k, v) AS (
  VALUES (1, 'a', 2), (2, 'a', 2), (3, 'a', 4),
         (4, 'a', CAST(NULL AS INT)), (5, 'b', 6), (6, 'b', 8),
         (7, 'c', CAST(NULL AS INT))
)
SELECT id, k, min(v) OVER (PARTITION BY k ORDER BY id NULLS LAST ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS result
FROM sample
ORDER BY id NULLS LAST;

id	k	result
1	a	2
2	a	2
3	a	2
4	a	4
5	b	6
6	b	6
7	c	NULL

Only NULL values

WITH sample(id, k, v) AS (
  VALUES (1, 'a', 2), (2, 'a', 2), (3, 'a', 4),
         (4, 'a', CAST(NULL AS INT)), (5, 'b', 6), (6, 'b', 8),
         (7, 'c', CAST(NULL AS INT))
)
SELECT min(v) AS result FROM sample WHERE v IS NULL;

result
NULL

Empty filtered sample

WITH sample(id, k, v) AS (
  VALUES (1, 'a', 2), (2, 'a', 2), (3, 'a', 4),
         (4, 'a', CAST(NULL AS INT)), (5, 'b', 6), (6, 'b', 8),
         (7, 'c', CAST(NULL AS INT))
)
SELECT min(v) AS result FROM sample WHERE id < 0;

result
NULL

Query Engine

Get started

Connect data

Run clusters

Query

Connect your tools

Secure and govern

Troubleshoot

Reference

Concepts

Syntax

Aggregate

Window

Arguments

Aggregate and window

Returns

Properties

Usage notes

Examples

Sample data

Integer values

Ignore NULLs

Aggregate the sample values

One result per group, including an all-NULL group

DISTINCT removes repeated input values

Window over all rows in each group

Running window

Sliding window with one preceding row

Only NULL values

Empty filtered sample

​Syntax

​Aggregate

​Window

​Arguments

​Aggregate and window

​Returns

​Properties

​Usage notes

​Examples

​Sample data

​Integer values

​Ignore NULLs

​Aggregate the sample values

​One result per group, including an all-NULL group

​DISTINCT removes repeated input values

​Window over all rows in each group

​Running window

​Sliding window with one preceding row

​Only NULL values

​Empty filtered sample

Syntax

Aggregate

Window

Arguments

Aggregate and window

Returns

Properties

Usage notes

Examples

Sample data

Integer values

Ignore NULLs

Aggregate the sample values

One result per group, including an all-NULL group

DISTINCT removes repeated input values

Window over all rows in each group

Running window

Sliding window with one preceding row

Only NULL values

Empty filtered sample