MAX¶

Syntax¶

Aggregate function

MAX( <expr> )

Window function

MAX( <expr> ) [ OVER ( [ PARTITION BY <expr1> ] [ ORDER BY <expr2> [ <window_frame> ] ] ) ]

For detailed window_frame syntax, see Window function 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, you can specify the DISTINCT keyword as an argument for the function, 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 Usage notes for window frames.

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 examples demonstrate how to use the MAX function.

Create a table and data:

CREATE OR REPLACE TABLE sample_table(k CHAR(4), d CHAR(4));

INSERT INTO sample_table 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 sample_table
    ORDER BY k, d;

Copy

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

Use the MAX function to retrieve the largest value in the column named d:

SELECT MAX(d) FROM sample_table;

Copy

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

Combine the GROUP BY clause with the MAX function to retrieve the largest values in each group (where each group is based on the value of column k):

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

Copy

+------+--------+                                                               
| K    | MAX(D) |
|------+--------|
| 1    | 5      |
| 2    | 2      |
| 3    | NULL   |
| NULL | 7      |
+------+--------+

Use a PARTITION BY clause to break the data into groups based on the value of k. This is similar to, but not identical to, using GROUP BY. In particular, 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 sample_table
  ORDER BY k, d;

Copy

+------+------+------------------------------+                                  
| 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 sample_table
  ORDER BY k, d;

Copy

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