RANK, DENSE_RANK and ROW_NUMBER :Oracle Analytic functions

Oracle 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

I would be discussing about RANK, DENSE_RANK and ROW_NUMBER analytics functions

Here is the general syntax

analytic_function([ arguments ]) OVER ([ query_partition_clause ] [ order_by_clause  ])

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

SQL> desc emp
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPNO                                     NOT NULL NUMBER(4)
 ENAME                                              VARCHAR2(10)
 JOB                                                VARCHAR2(9)
 MGR                                                NUMBER(4)
 HIREDATE                                           DATE
 SAL                                                NUMBER(7,2)
 COMM                                               NUMBER(7,2)
 DEPTNO                                             NUMBER(2)

SQL> select deptno ,count(*) from emp group by deptno;

    DEPTNO   COUNT(*)
---------- ----------
        30          6
        20          4
        10          3

SQL> select
deptno, ename, sal,
 row_number() over (partition by deptno order by sal) "row_number"
from
emp;

DEPTNO        ENAME       SAL    row_number
---------- ---------- ---------- ---------- 
10          CLARK          0        1 
10          MILLER         0        2
10          allen          28573    3
20          SMITH          8000     1
20          ADAMS          11000    2
20          SCOTT          30000    3
20          FORD           30000    4
30          JAMES          9500     1
30          MARTIN         12500    2
30          WARD           12500    3
30          TURNER         15000    4
30          ALLEN          16000    5
30          BLAKE          28500    6 

13 rows selected.

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.

SQL> select
deptno, ename, sal,
 rank() over (partition by deptno order by sal) "RANK"
from
emp;

DEPTNO        ENAME       SAL    RANK
---------- ---------- ---------- ---------- 
10          CLARK          0        1 
10          MILLER         0        2
10          allen          28573    3
20          SMITH          8000     1
20          ADAMS          11000    2
20          SCOTT          30000    3
20          FORD           30000    3
30          JAMES          9500     1
30          MARTIN         12500    2
30          WARD           12500    2
30          TURNER         15000    4
30          ALLEN          16000    5
30          BLAKE          28500    6 

13 rows selected.

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.

SQL> select
deptno, ename, sal,
 dense_rank() over (partition by deptno order by sal) "DENSE_RANK"
from
emp;

DEPTNO        ENAME       SAL    DENSE_RANK
---------- ---------- ---------- ---------- 
10          CLARK          0        1 
10          MILLER         0        2
10          allen          28573    3
20          SMITH          8000     1
20          ADAMS          11000    2
20          SCOTT          30000    3
20          FORD           30000    3
30          JAMES          9500     1
30          MARTIN         12500    2
30          WARD           12500    2
30          TURNER         15000    3
30          ALLEN          16000    4
30          BLAKE          28500    5 

13 rows selected.

We can put all the three in the single query also

select
  deptno, ename, sal,
  row_number() over (partition by deptno order by sal) "row_number",
     rank() over (partition by deptno order by sal) "rank",
          dense_rank() over (partition by deptno order by sal) "dense_rank"
        from
          emp;

    DEPTNO ENAME             SAL row_number       rank dense_rank
---------- ---------- ---------- ---------- ---------- ----------
        10 CLARK               0          1          1          1
        10 MILLER              0          2          1          1
        10 allen           28573          3          3          2
        20 SMITH            8000          1          1          1
        20 ADAMS           11000          2          2          2
        20 SCOTT           30000          3          3          3
        20 FORD            30000          4          3          3
        30 JAMES            9500          1          1          1
        30 MARTIN          12500          2          2          2
        30 WARD            12500          3          2          2
        30 TURNER          15000          4          4          3
        30 ALLEN           16000          5          5          4
        30 BLAKE           28500          6          6          5

13 rows selected.

We can use Row_number and RANK function in deleting the duplicating rows

delete from t
 where rowid IN ( select rid
                    from (select rowid rid, 
                                 row_number() over (partition by 
                         column_name
                                   order by rowid) rn
                            from t)
                   where rn <> 1);

These function are very useful for  for top-N and bottom-N queries.

The below SQL can be used to find the top salary in each dept

SQL> select * (select
deptno, ename, sal,
 row_number() over (partition by deptno order by sal) "row_number"
from
emp ) where row_number=1;

Leave a Reply