Mastering PostgreSQL 10

The rank and dense_rank functions

The rank() and dense_rank() functions are, in my judgment, the most prominent functions around. The rank() function returns the number of the current row within its window. Counting starts at one.

Here is an example:

test=# SELECT year, production,      rank() OVER (ORDER BY production) FROM t_oil WHERE country = 'Other Middle East' ORDER BY rank LIMIT 7;  year  | production | rank 
-------+------------+------ 
 2001  |  47        |  1 
 2004  |  48        |  2 
 2002  |  48        |  2 
 1999  |  48        |  2 
 2000  |  48        |  2 
 2003  |  48        |  2 
 1998  |  49        |  7  
(7 rows) 

The rank column will number those tuples in your dataset. Note that many rows in my sample are equal. Therefore, rank will jump from 2 to 7 directly, because many production values are identical. If ...