OLAP FUNCTIONS :-
(I) It is also called as “Analytical function”
(II) This is two types
- Rank
- Dense_rank
(III) These two functions are used to calculate rank of a particular value from the set of values.
Syntax :-
Rank( ) over (set of values)
Dense_rank() over [set of values]
Display enames,salary,with rank ranking should be based on salary
Select ename,sal,rank ( ) over (order by sal desc) as rank from emp.
- Ename sal rank
- B 500 1
- King 500 1
- Scott 300 3
- Ford 300 3
- Anum 200 5
rank functions generate “gaps”.
dense_rank functions won’t generate “Gaps”.
Select ename,sal dense rank ( ) over(order by sal desc) as rnk from emp
- Ename sal rnk
- B 503 1
- King 503 1
- Scott 300 2
- Ford 300 2
- Anum 200 3
Based on salary first then rank should be on hiredate
Select ename,sal dense_rank ( ) over(order by sal desc,hiredate) as rnk from emp.
- Ename sal rnk
- B 500 1
- King 500 2
- Scott 300 3
- Ford 300 4
- Anum 200 5
Ford the ranks of the employee with in the department based on their salaries.
Select ename,sal,deptno, dense_rank ( ) over(particular by deptno order by sal desc)as rnk from emp.
- Ename sal deptno rnk
- B 500 10 1
- King 500 10 1
- Scot 300 20 1
- Ford 200 20 2
- Anum 200 30 1