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.

See also

COUNT , SUM

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. The window_frame (either cumulative or sliding) specifies the subset of rows within the window for which the summed values are returned. If no window_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;

Output:

+------+------+
| K    | D    |
|------+------|
| 1    | 1    |
| 1    | 5    |
| 1    | 3    |
| 2    | 2    |
| 2    | NULL |
| 3    | NULL |
| NULL | 7    |
| NULL | 1    |
+------+------+

Use the MIN and MAX functions to retrieve the smallest and largest values in the column named “d”:

SELECT MIN(d), MAX(d) FROM minmax_example;

Output:

+--------+--------+
| MIN(D) | MAX(D) |
|--------+--------|
| 1      | 7      |
+--------+--------+

Combine the GROUP BY clause with the the MIN and MAX functions to retrieve the smallest and largest values in each group (where each group is based on the value of column “k”):

SELECT k, MIN(d), MAX(d)
  FROM minmax_example 
  GROUP BY k
  ORDER BY k;

Output:

+------+--------+--------+
| 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, using GROUP BY. In particular, note that GROUP BY produces one output row per group, while PARTITION BY produces one output row per input row.

SELECT k, d, MAX(d) OVER (PARTITION BY k)
  FROM minmax_example;

Output:

+------+------+------------------------------+
| K    | D    | MAX(D) OVER (PARTITION BY K) |
|------+------+------------------------------|
| 1    | 1    | 5                            |
| 1    | 5    | 5                            |
| 1    | 3    | 5                            |
| 2    | 2    | 2                            |
| 2    | NULL | 2                            |
| 3    | NULL | NULL                         |
| NULL | 7    | 7                            |
| NULL | 1    | 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.)

SELECT k, d, MAX(d) OVER (ORDER BY k ROWS BETWEEN 1 PRECEDING AND CURRENT ROW)
  FROM minmax_example;

Output:

+------+------+-------------------------------------------------------------------+
| K    | D    | MAX(D) OVER (ORDER BY K ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) |
|------+------+-------------------------------------------------------------------|
| 1    | 1    | 1                                                                 |
| 1    | 5    | 5                                                                 |
| 1    | 3    | 5                                                                 |
| 2    | 2    | 3                                                                 |
| 2    | NULL | 2                                                                 |
| 3    | NULL | NULL                                                              |
| NULL | 7    | 7                                                                 |
| NULL | 1    | 7                                                                 |
+------+------+-------------------------------------------------------------------+