MIN / MAX¶

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');

Copy

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    |
+------+------+

Copy

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;
+--------+--------+
| MIN(D) | MAX(D) |
|--------+--------|
| 1      | 7      |
+--------+--------+

Copy

Combine the GROUP BY clause with 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;
+------+--------+--------+
| K    | MIN(D) | MAX(D) |
|------+--------+--------|
| 1    | 1      | 5      |
| 2    | 2      | 2      |
| 3    | NULL   | NULL   |
| NULL | 1      | 7      |
+------+--------+--------+

Copy

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
  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                            |
+------+------+------------------------------+

Copy

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                                                                    |
+------+------+----------------------------------------------------------------------+

Copy