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