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