MAX Function

Description

The MAX function gets the greatest value of expressions of all rows. Only one expression is specified.

For expressions that return character strings, the string that appears later in alphabetical order becomes the maximum value; for those that return numbers, the greatest value becomes the maximum value.

For analytic function examples, see the following analytic function examples. For more information, see Overview.

Syntax

MAX ( [ { DISTINCT | DISTINCTROW } | UNIQUE | ALL ] expression )

• expression: Specifies an expression that returns a numeric or string value. An expression that returns a collection-type data is not allowed.
• ALL: Gets the maximum value for all data (default).
• DISTINCT or UNIQUE: Gets the maximum value without duplicates.

Example of Aggregate Function

The following example shows how to retrieve the maximum number of gold (gold) medals that Korea won in the Olympics in the demodb database.

SELECT MAX(gold) FROM participant WHERE nation_code = 'KOR';

    max(gold)

=============

           12

Example of Analytic Function

The following example shows how to output the number of gold medals by year and the maximum number of gold medals in history, acquired by the country whose nation_code code starts with 'AU'.

SELECT host_year, nation_code, gold,

MAX(gold) OVER (PARTITION BY nation_code) mx_gold

FROM participant WHERE nation_code like 'AU%' ORDER BY nation_code, host_year;

    host_year  nation_code                  gold      mx_gold

=============================================================

         1988  'AUS'                           3           17

         1992  'AUS'                           7           17

         1996  'AUS'                           9           17

         2000  'AUS'                          16           17

         2004  'AUS'                          17           17

         1988  'AUT'                           1            2

         1992  'AUT'                           0            2

         1996  'AUT'                           0            2

         2000  'AUT'                           2            2

         2004  'AUT'                           2            2