Rank
RANK calculates the rank of a value in a group of values. The return type is NUMBER.
Syntax:
RANK( expression ) WITHIN GROUP ( ORDER BY expression )
RANK( ) OVER ([query_partition_clause] order_by_clause)
Example 1:
SELECT RANK(1000, 300) WITHIN GROUP (ORDER BY SAL, COMM) from EMP;
Example 2:
SELECT e.EMPNO, e.ENAME, e.DEPTNO, e.SAL, RANK() OVER (PARTITION BY e.DEPTNO ORDER BY e.SAL DESC) as emp_rank FROM EMP e;
