MAX (window function)

Compute the maximum value for an expression based on a window definition and an optional frame clause.

MAX ([ expression ]) OVER { window_name | ( [ window_definition ] ) }

For the full window_definition syntax, see Syntax for Window Functions.

Note: The input expression for a MAX function cannot be a CHAR, VARCHAR, or BOOLEAN data type.

Example

For example, return the maximum seasonid from the season table and partition the results by the winners column. (In effect, this query shows when each winning team last won the league, alongside a list of all the other seasons when they were the winners.)

premdb=# select season_name, winners, 
max(seasonid) over(partition by winners) 
from season 
where winners is not null 
order by max desc;
 season_name |      winners      | max 
-------------+-------------------+-----
 2015-2016   | Leicester City    |  24
 2004-2005   | Chelsea           |  23
 2005-2006   | Chelsea           |  23
 2009-2010   | Chelsea           |  23
 2014-2015   | Chelsea           |  23
 2011-2012   | Manchester City   |  22
 2013-2014   | Manchester City   |  22
 1992-1993   | Manchester United |  21
 1993-1994   | Manchester United |  21
 1995-1996   | Manchester United |  21
 1996-1997   | Manchester United |  21
 1998-1999   | Manchester United |  21
 1999-2000   | Manchester United |  21
 2000-2001   | Manchester United |  21
 2002-2003   | Manchester United |  21
 2006-2007   | Manchester United |  21
 2007-2008   | Manchester United |  21
 2008-2009   | Manchester United |  21
 2010-2011   | Manchester United |  21
 2012-2013   | Manchester United |  21
 1997-1998   | Arsenal           |  12
 2001-2002   | Arsenal           |  12
 2003-2004   | Arsenal           |  12
 1994-1995   | Blackburn Rovers  |   3
(24 rows)