Categories: Aggregate, Window

MIN

Returns the minimum value among the non-NULL input expressions.

Syntax

MIN(expression NUMERIC) → NUMERIC

• expression: The expression from which to take the minimum value, across all rows.

Examples

MIN example: Aggregate function

SELECT MIN("total_amount") FROM "Samples"."samples.dremio.com"."NYC-taxi-trips";
-- -1430.0

MIN example: Window function

SELECT "trip_distance_mi", MIN("total_amount")
  OVER (PARTITION BY "trip_distance_mi") "min_total_amount"
FROM "Samples"."samples.dremio.com"."NYC-taxi-trips"
LIMIT 1;
-- trip_distance_mi, min_total_amount
-- 0.09, -52.5

MIN example: Window function with cumulative window frame

SELECT city, state, pop, MIN(pop)
  OVER (PARTITION BY state ORDER BY city RANGE BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) 
FROM Samples."samples.dremio.com"."zips.json";
-- city, state, pop, EXPR$3
-- 98791, AK, 5345, 0
-- AKHIOK, AK, 13309, 0
-- AKIACHAK, AK, 481, 0
-- ...

MIN example: Window function with sliding window frame

SELECT city, state, pop, MIN(pop)
  OVER (PARTITION BY state ORDER BY city ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING)
FROM Samples."samples.dremio.com"."zips.json";
-- city, state, pop, EXPR$3
-- 98791, AK, 5345, 481
-- AKHIOK, AK, 13309, 285
-- AKIACHAK, AK, 481, 285
-- ...

Usage Notes

The MIN function supports optional PARTITION BY, ORDER_BY, and cumulative and sliding window frame subclauses. See Window Functions for more information and syntax.