What is Oracle Rank Function ?
Oracle Rank() function
In Oracle PL/SQL, RANK function is a built in analytic function which is used to rank a record within a group of row.
The rank function can cause non-consecutive rankings if the tested values are the same. If you need to have the consecutive rankings you need to use dense_rank() function.
Used as an Analytic function:
Oracle Rank() function
In Oracle PL/SQL, RANK function is a built in analytic function which is used to rank a record within a group of row.
The rank function can cause non-consecutive rankings if the tested values are the same. If you need to have the consecutive rankings you need to use dense_rank() function.
Example Syntax:
Used as an Aggregate function:
Used as an Aggregate function:
RANK (expression) WITHIN GROUP (ORDER_BY expression [ASC | DESC] NULLS [FIRST | LAST] )
Used as an Analytic function:
RANK () OVER (PARTITION BY expression ORDER BY expression)
Example Usage:
The below SQL query uses demonstrates analytic behavior of RANK function. It ranks the salary of the employees working in the same department.
The below SQL query uses demonstrates analytic behavior of RANK function. It ranks the salary of the employees working in the same department.
SELECT DEPT, STUDENT_ID, RANK() OVER
(PARTITION BY DEPT ORDER BY TOTAL_MARKS) RANK
FROM STUDENTS_TABLE
/
DEPT STUDENT_ID RANK
---------- ---------- ---------
100 100001 1
100 100009 2
101 100005 1
101 100004 2
101 100002 3
102 100006 1
102 100007 2
7 rows selected.