Mastering PostgreSQL 12 - Third Edition

The rank and dense_rank functions

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

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, the rank will jump from 2 to 7 directly, because many production values are identical. ...