Oracle: Rank, Dense_Rank, Row_Number Analytic Functions

Analytic functions compute an aggregate value based on a group of rows called window which determines the range of rows used to perform the calculations for the current row. Following are most used Analytic functions.
- RANK, DENSE_RANK and ROW_NUMBER
- LAG and LEAD
- FIRST_VALUE and LAST_VALUE

ROW_NUMBER assigns a unique number to each row of same window in the ordered sequence of rows specified by order_by_clause.

RANK is almost same as ROW_NUMBER but rows with equal values, with in same window, for on which order by clause is specified receive the same rank but next row receives RANK as per it ROW_NUMBER. In the following example MARTIN and WARD has same RANK 2, TURNER next to WARD in same group receives RANK 4.

DENSE_RANK is almost same as the RANK, but it does not leaves gap between rows if one or more values are same. Like in following example TURNER next to WARD in same group receives DENSE_RANK 3.

Lets understand the difference among ROW_NUMBER, RANK and DENSE_RANK with the following example:

SQL> select
  2    deptno, ename, sal,
  3    row_number() over (partition by deptno order by sal) "row_number",
  4    rank() over (partition by deptno order by sal) "rank",
  5    dense_rank() over (partition by deptno order by sal) "dense_rank"
  6  from
  7    scott.emp;

    DEPTNO ENAME             SAL row_number       rank dense_rank
---------- ---------- ---------- ---------- ---------- ----------
        10 MILLER           1300          1          1          1
        10 CLARK            2450          2          2          2
        10 KING             5000          3          3          3
        20 SMITH             800          1          1          1
        20 ADAMS            1100          2          2          2
        20 JONES            2975          3          3          3
        20 SCOTT            3000          4          4          4
        20 FORD             3000          5          4          4
        30 JAMES             950          1          1          1
        30 MARTIN           1250          2          2          2
        30 WARD             1250          3          2          2
        30 TURNER           1500          4          4          3
        30 ALLEN            1600          5          5          4
        30 BLAKE            2850          6          6          5

14 rows selected.

6 comments:

AnonymousFebruary 5, 2010 at 11:28 PM
nice example :)
RajatFebruary 6, 2010 at 12:56 PM
Hi Nimish, i have just started with Oracle Analytics and exploring on this...your blog will really help me learning and mastering it.

Here is one more example, this truly shows the power of Oracle analytics...

Here we are trying to collate all the employee ids into single row respective to their departments. Emp ids will be comma separated.

Normal SQL will take lot of efforts and coding to get the same out put.
**********************************************

create table tab_test(deptno number(20), emp_no number(20));

insert into tab_test values (10, 101)

/

insert into tab_test values (10, 102)

/

insert into tab_test values (10, 103)

/

insert into tab_test values (10, 104)

/

insert into tab_test values (10, 105)

/

insert into tab_test values (20, 203)

/

insert into tab_test values (20, 204)

/

insert into tab_test values (20, 205)

/

insert into tab_test values (30, 310)

/

insert into tab_test values (30, 320)

/

insert into tab_test values (30, 330)

/

COMMIT;

SELECT deptno,

LTRIM(MAX(SYS_CONNECT_BY_PATH(emp_no,','))

KEEP (DENSE_RANK LAST ORDER BY curr),',') AS employees

FROM (SELECT deptno,

emp_no,

ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY emp_no) AS curr,

ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY emp_no) -1 AS prev

FROM tab_test)

GROUP BY deptno

CONNECT BY prev = PRIOR curr AND deptno = PRIOR deptno

START WITH curr = 1;
dicky XIIP1December 6, 2013 at 9:24 AM
nice :D
dicky XIIP1December 6, 2013 at 9:24 AM
nice :D
Sharad NawadeJune 26, 2019 at 11:27 AM
Dense rank and row number is same , rank ranks the same rank for duplicate value