Mastering PostgreSQL 9.6

rank and dense_rank functions

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 rows. Note that many rows in my sample are equal. Therefore rank will jump from 2 to 7 directly. If you want to avoid that, dense_rank() function is the way to go: ...