Product Documentation

Appearance

MAX ​

Return the maximum value for an expression (numeric, character, or datetime).

MAX(expression)

Note: The input expression for a MAX function cannot be a Boolean data type.

The following example returns the closing matchday for each season.

premdb=# SELECT seasonid, MAX(matchday) 
FROM match WHERE matchday IS NOT NULL 
GROUP BY seasonid ORDER BY seasonid;
 seasonid |         max         
----------+---------------------
       2 | 1994-05-08 00:00:00
       3 | 1995-05-14 00:00:00
       4 | 1996-05-04 00:00:00
       5 | 1997-05-11 00:00:00
       6 | 1998-05-10 00:00:00
       7 | 1999-05-16 00:00:00
       8 | 2000-05-14 00:00:00
       9 | 2001-05-19 00:00:00
      10 | 2002-05-11 00:00:00
...

The following example returns the "maximum" nickname value from the team table:

premdb=# SELECT MAX(nickname) FROM team;
  max   
--------
 Wolves
(1 row)

The following example returns the most goals scored in a single game per season. The MAX function is applied to substrings of the ftscore column (full-time score). The first substring returns the goals scored by the home team, and the second the goals scored by the away team.

premdb=# SELECT seasonid, 
MAX(SUBSTR(ftscore,1,1)::SMALLINT + SUBSTR(ftscore,3,3)::SMALLINT) AS maxgoals_per_match 
FROM matchcopy GROUP BY seasonid ORDER BY 1;
 seasonid | maxgoals_per_match 
----------+--------------------
       1 |                  8
       2 |                  9
       3 |                  9
       4 |                  8
       5 |                  9
       6 |                  9
       7 |                  9
       8 |                  9
       9 |                  8
      10 |                  8
      11 |                  8
      12 |                  8
      13 |                  9
      14 |                  7
      15 |                  8
      16 |                 11
      17 |                  8
      18 |                 10
      19 |                  8
      20 |                 10
      21 |                 10
      22 |                  9
...

MIN and MAX with NaN Values ​

Yellowbrick supports NaN (not a number) as a floating-point value. When MIN and MAX functions are applied to data that contains NaN values, NaN is returned as the maximum value. NaN sorts as greater than all other floating-point values, including +inf (infinity).

Parent topic:Aggregate Functions