Skip to content

RANK

Rank a result set based on an ordered set of rows.

RANK() OVER { window_name | ( window_definition ) }

The RANK() function itself does not take any arguments, but the empty parentheses are required. The ORDER BY clause in the window_definition is required. The frame clause, if specified, is ignored. For the full window_definition syntax, see Syntax for Window Functions.

The following example returns the 25 highest-scoring home wins, based on an ordered set of ftscore values. (Note that this query orders on the first number in the ftscore column so a score of 7-4 ranks lower than a score of 8-0, for example.)

SELECT season_name, h.name, a.name, ftscore, 
RANK() OVER(ORDER BY ftscore DESC) 
FROM season s, hometeam h, awayteam a, match m 
WHERE s.seasonid=m.seasonid AND h.htid=m.htid AND a.atid=m.atid 
ORDER BY 5,1 LIMIT 25;
season_name |       name        |        name         | ftscore | rank 
-------------+-------------------+---------------------+---------+------
 2009-2010   | Tottenham Hotspur | Wigan Athletic      | 9-1     |    1
 1994-1995   | Manchester United | Ipswich Town        | 9-0     |    2
 2011-2012   | Manchester United | Arsenal             | 8-2     |    3
 2007-2008   | Middlesbrough     | Manchester City     | 8-1     |    4
 1999-2000   | Newcastle United  | Sheffield Wednesday | 8-0     |    5
 2009-2010   | Chelsea           | Wigan Athletic      | 8-0     |    5
 2012-2013   | Chelsea           | Aston Villa         | 8-0     |    5
 2007-2008   | Portsmouth        | Reading             | 7-4     |    8
 2012-2013   | Arsenal           | Newcastle United    | 7-3     |    9
 1997-1998   | Blackburn Rovers  | Sheffield Wednesday | 7-2     |   10
 1999-2000   | Tottenham Hotspur | Southampton         | 7-2     |   10
 2009-2010   | Chelsea           | Sunderland          | 7-2     |   10
 1992-1993   | Blackburn Rovers  | Norwich City        | 7-1     |   13
 1993-1994   | Newcastle United  | Swindon Town        | 7-1     |   13
 1994-1995   | Aston Villa       | Wimbledon           | 7-1     |   13
 1996-1997   | Everton           | Southampton         | 7-1     |   13
 1996-1997   | Newcastle United  | Tottenham Hotspur   | 7-1     |   13
 1998-1999   | Liverpool         | Southampton         | 7-1     |   13
 1999-2000   | Manchester United | West Ham United     | 7-1     |   13
 2001-2002   | Blackburn Rovers  | West Ham United     | 7-1     |   13
 2007-2008   | Everton           | Sunderland          | 7-1     |   13
 2009-2010   | Chelsea           | Aston Villa         | 7-1     |   13
 2010-2011   | Manchester United | Blackburn Rovers    | 7-1     |   13
 2011-2012   | Arsenal           | Blackburn Rovers    | 7-1     |   13
 1995-1996   | Blackburn Rovers  | Nottingham Forest   | 7-0     |   25
(25 rows)

You can partition this result by season, for example:

SELECT season_name, h.name, a.name, ftscore, 
RANK() OVER(PARTITION BY season_name ORDER BY ftscore DESC) 
FROM season s, hometeam h, awayteam a, match m 
WHERE s.seasonid=m.seasonid AND h.htid=m.htid AND a.atid=m.atid 
ORDER BY 1,5;

Parent topic:Window Functions