column values vs dense rank vs rank vs row number
suggest changehere 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 isdense_rank
shows 1 - FIRST value not changed. fourth rowi=2
, it is second value of i met, sodense_rank
shows 2, andso for the next row. Then it meets valuei=3
at 6th row, so it show 3. Same for the rest two values of i. So the last value ofdense_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 meansi=2
(new value) was met at row 4. Samei=3
was met at row 6. Etc..
Found a mistake? Have a question or improvement idea?
Let me know.
Table Of Contents