MAX

Calculates the largest value of the values that are not NULL in expr. If all values in expr are NULL, NULL is returned.

Syntax

Aggregate Function

MAX([DISTINCT] expr)

Window Function

MAX(expr) OVER ([PARTITION BY part]
                 [ORDER BY ord [ASC|DESC] [window_frame]]
                )

Arguments

expr: A (qualified) column name.

---

part (optional): Expression to partition by.

---

ord (optional): Optional expression to sort within each partition.

---

window_frame (cumulative or sliding): Defines the subset of rows within the window for which the aggregated values should be returned.

Usage Notes

• As a window function (with an OVER clause. See also Working with window functions):

  • A window frame (cumulative or sliding) must be defined. The default value for the window frame, if not specified, is a cumulative window frame RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

  • The use of the DISTINCT keyword when this function is used as a window function is ignored.

Example 1 | Aggregate Function

select  max(col1) as result1,
        max(DISTINCT col1) as result2,
        max(col2) as result3,
        max(DISTINCT col2) as result4,
        max(ord1) as result5, 
        max(grp1) as result6
            from  ( select 'a' as ord1, 'g1' as grp1, '10' as col1, 10 as col2
                    union all 
                    select 'b' as ord1, 'g1' as grp1, '20' as col1, 20 as col2
                    union all
                    select 'c' as ord1, 'g1' as grp1, '20' as col1, 20 as col2
                    union all 
                    select 'a' as ord1, 'g2' as grp1, '30' as col1, NULL as col2 
                    union all 
                    select 'b' as ord1, 'g2' as grp1, '30' as col1, NULL as col2 
                    union all 
                    select 'c' as ord1, 'g2' as grp1, '40' as col1, 40 as col2
                    union all 
                    select 'a' as ord1, 'g3' as grp1, '50' as col1, NULL as col2
                    union all 
                    select 'b' as ord1, 'g3' as grp1, '50' as col1, NULL as col2
                    union all 
                    select 'c' as ord1, 'g3' as grp1, '60' as col1, 60 as col2
                    union all
                    select 'd' as ord1, 'g3' as grp1, '50' as col1, 50 as col2);

+---------+---------+---------+---------+---------+---------+
| RESULT1 | RESULT2 | RESULT3 | RESULT4 | RESULT5 | RESULT6 |
+---------+---------+---------+---------+---------+---------+
| 60      | 60      | 60      | 60      | d       | g3      |
+---------+---------+---------+---------+---------+---------+

Example 2 | Window Function

select col1, grp1, ord1,
       max(col1) OVER(PARTITION BY grp1) as result1,
       max(col1) OVER(PARTITION BY grp1 ORDER BY ord1 ASC) as result2,
       max(col1) OVER(PARTITION BY grp1 ORDER BY ord1 DESC) as result3
            from  ( select 'a' as ord1, 'g1' as grp1, '10' as col1, 10 as col2
                    union all 
                    select 'b' as ord1, 'g1' as grp1, '20' as col1, 20 as col2
                    union all
                    select 'c' as ord1, 'g1' as grp1, '20' as col1, 20 as col2
                    union all 
                    select 'a' as ord1, 'g2' as grp1, '30' as col1, NULL as col2 
                    union all 
                    select 'b' as ord1, 'g2' as grp1, '30' as col1, NULL as col2 
                    union all 
                    select 'c' as ord1, 'g2' as grp1, '40' as col1, 40 as col2
                    union all 
                    select 'a' as ord1, 'g3' as grp1, '50' as col1, NULL as col2
                    union all 
                    select 'b' as ord1, 'g3' as grp1, '50' as col1, NULL as col2
                    union all 
                    select 'c' as ord1, 'g3' as grp1, '60' as col1, 60 as col2
                    union all
                    select 'd' as ord1, 'g3' as grp1, '50' as col1, 50 as col2);

+------+------+------+---------+---------+---------+
| COL1 | GRP1 | ORD1 | RESULT1 | RESULT2 | RESULT3 |
+------+------+------+---------+---------+---------+
| 10   | g1   | a    | 20      | 10      | 20      |
| 20   | g1   | b    | 20      | 20      | 20      |
| 20   | g1   | c    | 20      | 20      | 20      |
| 30   | g2   | a    | 40      | 30      | 40      |
| 30   | g2   | b    | 40      | 30      | 40      |
| 40   | g2   | c    | 40      | 40      | 40      |
| 50   | g3   | a    | 60      | 50      | 60      |
| 50   | g3   | b    | 60      | 50      | 60      |
| 60   | g3   | c    | 60      | 60      | 60      |
| 50   | g3   | d    | 60      | 60      | 50      |
+------+------+------+---------+---------+---------+

Example 3 | Window Function with WINDOWING

In this example, a sliding window with a width of 4 rows is created. For this window, the highest value is returned. A single partition is used, so there is no PARTITION BY clause in the OVER() clause.

  select col1, 
         grp1, 
         max(col1) OVER(ORDER BY grp1 ROWS BETWEEN 3 PRECEDING AND CURRENT ROW) as result1
            from  ( select 'a' as ord1, 'g1' as grp1, '10' as col1, 10 as col2
                    union all 
                    select 'b' as ord1, 'g1' as grp1, '20' as col1, 20 as col2
                    union all
                    select 'c' as ord1, 'g1' as grp1, '20' as col1, 20 as col2
                    union all 
                    select 'a' as ord1, 'g2' as grp1, '30' as col1, NULL as col2 
                    union all 
                    select 'b' as ord1, 'g2' as grp1, '30' as col1, NULL as col2 
                    union all 
                    select 'c' as ord1, 'g2' as grp1, '40' as col1, 40 as col2
                    union all 
                    select 'a' as ord1, 'g3' as grp1, '50' as col1, NULL as col2
                    union all 
                    select 'b' as ord1, 'g3' as grp1, '50' as col1, NULL as col2
                    union all 
                    select 'c' as ord1, 'g3' as grp1, '60' as col1, 60 as col2
                    union all
                    select 'd' as ord1, 'g3' as grp1, '50' as col1, 50 as col2);

+------+------+---------+
| COL1 | GRP1 | RESULT1 |
+------+------+---------+
| 10   | g1   | 10      |
| 20   | g1   | 20      |
| 20   | g1   | 20      |
| 30   | g2   | 30      |
| 30   | g2   | 30      |
| 40   | g2   | 40      |
| 50   | g3   | 50      |
| 50   | g3   | 50      |
| 60   | g3   | 60      |
| 50   | g3   | 60      |  
+------+------+---------+