Training Facility , Capgemini @ Pune - Freshers training program
Rank ,
Dense_Rank() and Row_Number()
The RANK, DENSE_RANK and
ROW_NUMBER functions are used to retrieve an increasing integer value. They
start with a value based on the condition imposed by the ORDER BY clause. All
of these functions require the ORDER BY clause to function properly. In case of
partitioned data, the integer counter is reset to 1 for each partition.
RANK
Analytic Function
The basic description for
the RANK analytic function is shown below. The
analytic clause is described in more detail here.
RANK() OVER ([ query_partition_clause ]
order_by_clause)
Let's assume we want to assign a
sequential order, or rank, to people within a department based on salary, we
might use the RANK function like this.
select empno,
deptno,
sal,
rank() over (partition by deptno order by sal) as myrank
from
emp;
EMPNO DEPTNO SAL
MYRANK
---------- ---------- ---------- ----------
7934 10 1300 1
7782
10 2450 2
7839 10 5000 3
7369 20 800 1
7876 20 1100 2
7566 20 2975 3
7788 20 3000 4
7902 20 3000 4
7900 30 950 1
7654 30 1250 2
7521 30 1250 2
7844 30 1500 4
7499 30 1600 5
7698 30 2850 6
SQL>
What we see here is where two
people have the same salary they are assigned the same rank. When multiple rows
share the same rank the next rank in the sequence is not consecutive. This is
like olympic medaling in that if two people share the gold, there is no silver
medal etc.
select *
from
(select empno,
deptno,
sal,
rank() over (partition by deptno order by sal) as myrank
from emp)
where
myrank <= 2;
EMPNO DEPTNO SAL
MYRANK
---------- ---------- ---------- ----------
7934 10 1300 1
7782 10 2450
2
7369 20 800 1
7876 20 1100 2
7900 30 950 1
7521 30 1250 2
7654 30 1250 2
SQL>
DENSE_RANK
Analytic Function
The basic description for
the DENSE_RANK analytic function is shown below. The
analytic clause is described in more detail here.
DENSE_RANK() OVER([ query_partition_clause ]
order_by_clause)
The DENSE_RANK function acts like the RANK function except that it
assigns consecutive ranks, so this is not like olympic medaling.
select empno,
deptno,
sal,
dense_rank()
over (partition by deptno order by sal) as myrank
from
emp;
EMPNO DEPTNO SAL
MYRANK
---------- ---------- ---------- ----------
7934 10 1300 1
7782 10 2450 2
7839
10 5000 3
7369 20 800 1
7876 20 1100 2
7566 20 2975 3
7788 20 3000 4
7902 20 3000 4
7900 30 950 1
7654 30 1250 2
7521 30 1250 2
7844 30 1500 3
7499 30 1600 4
7698 30 2850 5
SQL>
Differences between RANK and DENSE_RANK
RANK and DENSE_RANK will assign the grades the same rank depending on how they fall compared to the other values. However, RANK will then skip the next available ranking value whereas DENSE_RANK would still use the next chronological ranking value
select ename,sal, deptno, rank() over (order by sal) as myrank from emp
ROW_NUMBER Function
Unlike the RANK and DENSE_RANK functions, the ROW_NUMBER
function simply returns the row number of the sorted records starting with 1.
For example, if RANK and DENSE_RANK functions of the first two records in the
ORDER BY column are equal, both of them are assigned 1 as their RANK and
DENSE_RANK. However, the ROW_NUMBER function will assign values 1 and 2 to
those rows without taking the fact that they are equally into account. Execute
the following script to see the ROW_NUMBER function in action.
select ename, deptno,
row_number() over ( order by deptno desc) as rowrank from emp;
Comments