Analytical Functions In Oracle

 

Analytical Functions In Oracle

Analytical Functions :

1.        ROW_NUMBER()

2.        DENSE_RANK()

3.        RANK()

 

 

1.   ROW_NUMBER():

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

Example :

select

deptno, ename, sal,

 row_number() over (order by sal desc) "row_number"

from

emp;

 

DEPTNO	ENAME	SAL	ROW_NUMBER
10	KING	5000	1
20	SCOTT	3000	2
20	FORD	3000	3
20	JONES	2975	4
30	BLAKE	2850	5
10	CLARK	2450	6
30	ALLEN	1600	7
30	TURNER	1500	8
10	MILLER	1300	9
30	MARTIN	1250	10
30	WARD	1250	11
20	ADAMS	1100	12
30	JAMES	950	13
20	SMITH	800	14

 

 

 

 

 

 

2.    RANK()

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.

Example:

select

deptno, ename, sal,

 rank() over (partition  By DEPTNO order by sal desc) "RANK"

from

emp

order by deptno asc;

 

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

 

 

 

 

 

 

 

 

 

 

3.    DENSE_RANK()

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.

Example:

select

deptno,

ename,

sal,

               dense_rank() over (partition by deptno order by sal desc) DENSE_RANK

from

emp;

 

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

 

 

 

 

 

 

 

 

 

Combined Query in which all above functions used:

 

SELECT

    empno,

    ename,

    DEPTNO,

    sal,

    ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY sal DESC) ROW_NUMBER,

    RANK() OVER(PARTITION BY DEPTNO ORDER BY sal DESC) rank,

    DENSE_RANK() OVER(PARTITION BY DEPTNO ORDER BY sal DESC) Dense_rank

FROM

    emp;

 

EMPNO	ENAME	DEPTNO	SAL	ROW_NUMBER	DENSE_RANK	RANK
7839	KING	10	5000	1	1	1
7782	CLARK	10	2450	2	2	2
7934	MILLER	10	1300	3	3	3
7788	SCOTT	20	3000	1	1	1
7902	FORD	20	3000	2	1	1
7566	JONES	20	2975	3	3	2
7876	ADAMS	20	1100	4	4	3
7369	SMITH	20	800	5	5	4
7698	BLAKE	30	2850	1	1	1
7499	ALLEN	30	1600	2	2	2
7844	TURNER	30	1500	3	3	3
7521	WARD	30	1250	4	4	4
7654	MARTIN	30	1250	5	4	4
7900	JAMES	30	950	6	6	5