Product Documentation

Appearance

All platforms

DENSE_RANK ​

Rank a result set based on an ordered set of rows. Unlike RANK, do not skip any ranking values when ties occur in the result.

DENSE_RANK() OVER { window_name | ( window_definition ) }

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.

Compare the results of this example with the equivalent example for the RANK function.

premdb=# SELECT season_name, h.name, a.name, ftscore, 
DENSE_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 | dense_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     |          6
 2012-2013   | Arsenal           | Newcastle United    | 7-3     |          7
 1997-1998   | Blackburn Rovers  | Sheffield Wednesday | 7-2     |          8
 1999-2000   | Tottenham Hotspur | Southampton         | 7-2     |          8
 2009-2010   | Chelsea           | Sunderland          | 7-2     |          8
 1992-1993   | Blackburn Rovers  | Norwich City        | 7-1     |          9
 1993-1994   | Newcastle United  | Swindon Town        | 7-1     |          9
 1994-1995   | Aston Villa       | Wimbledon           | 7-1     |          9
 1996-1997   | Everton           | Southampton         | 7-1     |          9
 1996-1997   | Newcastle United  | Tottenham Hotspur   | 7-1     |          9
 1998-1999   | Liverpool         | Southampton         | 7-1     |          9
 1999-2000   | Manchester United | West Ham United     | 7-1     |          9
 2001-2002   | Blackburn Rovers  | West Ham United     | 7-1     |          9
 2007-2008   | Everton           | Sunderland          | 7-1     |          9
 2009-2010   | Chelsea           | Aston Villa         | 7-1     |          9
 2010-2011   | Manchester United | Blackburn Rovers    | 7-1     |          9
 2011-2012   | Arsenal           | Blackburn Rovers    | 7-1     |          9
 1995-1996   | Blackburn Rovers  | Nottingham Forest   | 7-0     |         10
(25 rows)