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;