postgresql column values vs dense_rank vs rank vs row_number


Example

here you can find the functions.

With the table wf_example created in previous example, run:

select i
  , dense_rank() over (order by i)
  , row_number() over ()
  , rank() over (order by i)
from wf_example

The result is:

 i | dense_rank | row_number | rank
---+------------+------------+------
 1 |          1 |          1 |    1
 1 |          1 |          2 |    1
 1 |          1 |          3 |    1
 2 |          2 |          4 |    4
 2 |          2 |          5 |    4
 3 |          3 |          6 |    6
 4 |          4 |          7 |    7
 5 |          5 |          8 |    8
  • dense_rank orders VALUES of i by appearance in window. i=1 appears, so first row has dense_rank, next and third i value does not change, so it is dense_rank shows 1 - FIRST value not changed. fourth row i=2, it is second value of i met, so dense_rank shows 2, andso for the next row. Then it meets value i=3 at 6th row, so it show 3. Same for the rest two values of i. So the last value of dense_rank is the number of distinct values of i.

  • row_number orders ROWS as they are listed.

  • rank Not to confuse with dense_rank this function orders ROW NUMBER of i values. So it starts same with three ones, but has next value 4, which means i=2 (new value) was met at row 4. Same i=3 was met at row 6. Etc..