- Categories:
Aggregate Functions (General) , Window Functions (Window Frame)
MIN / MAX¶
Returns the minimum or maximum value for the records within expr
. NULL values are ignored unless all the records are NULL, in which case a NULL value is returned.
Syntax¶
Aggregate function
MAX( <expr> )
MIN( <expr> )
Window function
MAX( <expr> ) [ OVER ( [ PARTITION BY <expr1> ] [ ORDER BY <expr2> [ <window_frame> ] ] ) ]
MIN( <expr> ) [ OVER ( [ PARTITION BY <expr1> ] [ ORDER BY <expr2> [ <window_frame> ] ] ) ]
For details about window_frame
syntax, see Window Frame Syntax and Usage.
Returns¶
The data type of the returned value is the same as the data type of the input values.
Usage Notes¶
For compatibility with other systems, the DISTINCT keyword can be specified as an argument for MIN or MAX, but it does not have any effect.
If the function is called as a window function, the window can include an optional
window_frame
. Thewindow_frame
(either cumulative or sliding) specifies the subset of rows within the window for which the summed values are returned. If nowindow_frame
is specified, the default is the following cumulative window frame (in accordance with the ANSI standard for window functions):RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
For more details about window frames, including syntax and examples, see Window Frame Syntax and Usage.
Collation Details¶
The comparisons follow the collation based on the input arguments’ collations and precedences.
The collation of the result is the same as the collation of the input.
Examples¶
The following code demonstrates the use of MIN
and MAX
:
Create a table and data:
CREATE OR REPLACE TABLE minmax_example(k CHAR(4), d CHAR(4)); INSERT INTO minmax_example VALUES ('1', '1'), ('1', '5'), ('1', '3'), ('2', '2'), ('2', NULL), ('3', NULL), (NULL, '7'), (NULL, '1');Display the data:
SELECT k, d FROM minmax_example ORDER BY k, d; +------+------+ | K | D | |------+------| | 1 | 1 | | 1 | 3 | | 1 | 5 | | 2 | 2 | | 2 | NULL | | 3 | NULL | | NULL | 1 | | NULL | 7 | +------+------+Use the
MIN
andMAX
functions to retrieve the smallest and largest values in the column named “d”:SELECT MIN(d), MAX(d) FROM minmax_example; +--------+--------+ | MIN(D) | MAX(D) | |--------+--------| | 1 | 7 | +--------+--------+Combine the
GROUP BY
clause with theMIN
andMAX
functions to retrieve the smallest and largest values in each group (where each group is based on the value of columnk
):SELECT k, MIN(d), MAX(d) FROM minmax_example GROUP BY k ORDER BY k; +------+--------+--------+ | K | MIN(D) | MAX(D) | |------+--------+--------| | 1 | 1 | 5 | | 2 | 2 | 2 | | 3 | NULL | NULL | | NULL | 1 | 7 | +------+--------+--------+Use a
PARTITION BY
clause to break the data into groups based on the value of k. Note that this is similar to, but not identical to, usingGROUP BY
. In particular, note thatGROUP BY
produces one output row per group, whilePARTITION BY
produces one output row per input row.SELECT k, d, MAX(d) OVER (PARTITION BY k) FROM minmax_example ORDER BY k, d; +------+------+------------------------------+ | K | D | MAX(D) OVER (PARTITION BY K) | |------+------+------------------------------| | 1 | 1 | 5 | | 1 | 3 | 5 | | 1 | 5 | 5 | | 2 | 2 | 2 | | 2 | NULL | 2 | | 3 | NULL | NULL | | NULL | 1 | 7 | | NULL | 7 | 7 | +------+------+------------------------------+Use a windowing
ORDER BY
clause to create a sliding window two rows wide, and output the highest value within that window. (Remember that ORDER BY in the windowing clause is separate from ORDER BY at the statement level.) This example uses a single partition, so there is no PARTITION BY clause in the OVER() clause.SELECT k, d, MAX(d) OVER (ORDER BY k, d ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) FROM minmax_example ORDER BY k, d; +------+------+----------------------------------------------------------------------+ | K | D | MAX(D) OVER (ORDER BY K, D ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) | |------+------+----------------------------------------------------------------------| | 1 | 1 | 1 | | 1 | 3 | 3 | | 1 | 5 | 5 | | 2 | 2 | 5 | | 2 | NULL | 2 | | 3 | NULL | NULL | | NULL | 1 | 1 | | NULL | 7 | 7 | +------+------+----------------------------------------------------------------------+