| René Nyffenegger's collection of things on the web | |
|
René Nyffenegger on Oracle - Most wanted - Feedback
|
dense_rank vs rank vs row_number [Oracle SQL] | ||
create table some_table ( a number, b varchar2(10) ); insert into some_table values (5, '*'); insert into some_table values (2, '*'); insert into some_table values (5, '*'); insert into some_table values (3, '*'); insert into some_table values (5, '*'); insert into some_table values (3, '*'); insert into some_table values (2, '*'); insert into some_table values (4, '#'); insert into some_table values (8, '#'); insert into some_table values (4, '#'); insert into some_table values (8, '#'); insert into some_table values (4, '#'); select dense_rank() over (partition by b order by a) dr,
rank() over (partition by b order by a) rk,
row_number() over (partition by b order by a) rn,
a,b
from some_table;
DR RK RN A B
---------- ---------- ---------- ---------- ----------
1 1 1 4 #
1 1 2 4 #
1 1 3 4 #
2 4 4 8 #
2 4 5 8 #
1 1 1 2 *
1 1 2 2 *
2 3 3 3 *
2 3 4 3 *
3 5 5 5 *
3 5 6 5 *
3 5 7 5 *
drop table some_table; |