Tuesday, August 19, 2014

Rank Function in PL/SQL


Rank :

In Oracle/PLSQL, the rank function returns the rank of a value in a group of values.

The rank function can be used two ways - as an Aggregate function or as an Analytic function.

---
Rank used as aggregate function:

select rank (800) within  group (order by sal) rank from emp;

select rank (850) within  group (order by sal) rank from emp;

select rank (5000) within  group (order by sal) rank from emp;


Rank used as analytical function:

As an Analytic function, the rank returns the rank of each row of a query

select ename, sal,
rank() OVER (ORDER BY sal) rank
from emp;

Reactions:

0 comments:

Post a Comment