Analytical Function 1

[miraora]

Hello Masters;
I have confusion. I have gone through many documnetation but still not clear how the ranks are decided.
In the following code. How eight is given rank as 1, not nine, same as five is given rank two instead of seven. So i would like to know how this ranks are decide. i know the densed rank keep the sequence even though the tie in th data.

create table top_n_test (
    a number,
    b varchar2(10)
   );
insert into top_n_test values (1, 'one');
insert into top_n_test values (2, 'two');
insert into top_n_test values (3, 'three');
insert into top_n_test values (4, 'four');
insert into top_n_test values (5, 'five');
insert into top_n_test values (6, 'six');
insert into top_n_test values (7, 'seven');
insert into top_n_test values (8, 'eight');
insert into top_n_test values (9, 'nine');
commit;


select
    a,b,
     dense_rank() over (order by b) RD,rank() over (order 
     by b) R
from
      top_n_test;

result

Code:

A	B	RD	R
8	eight	1	1
5	five	2	2
4	four	3	3
9	nine	4	4
1	one	5	5
7	seven	6	6
6	six	7	7
3	three	8	8
2	two	9	9

 

[Dagon]

The problem is that you are ordering by column b, which is a varchar2. 'Eight' (not 8) appears at the top because it is first alphabetically. If you ordered numerically, 1 would be at the top.

 

[miraora]

Thank you for your help