Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 The Oracle PL/SQL RANK Function      [Return To Index] Jump to:  
  Looking for the original pages? (formerly called "Morgan's Library") You can find them here.

Term: RANK

Definition:
In Oracle PL/SQL, RANK function is a built in analytic function which is used to rank a record within a group of rows. Its return type is number and serves for both aggregate and analytic purpose in SQL.

Example Syntax:

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.

SELECT DEPT, EMPNO, RANK() OVER 
(PARTITION BY DEPT ORDER_BY SAL) RANK
FROM EMPLOYEE
/

DEPT EMPNO RANK
---------- ---------- ----------
10 100 1
10 110 2
20 140 1
20 170 2
20 120 3
30 130 1
30 180 2
40 150 1
50 160 1

9 rows selected.



Related Links:

Related Code Snippets:
  • DENSE_RANK - Ranks items in a group leaving no gaps in ranking sequence when there are ties.
  • PERCENT_RANK - For a row r, PERCENT_RANK calculates the rank of r minus 1, divided by 1 less than ...
  • Number Functions: RANK - Calculates the rank of a value in a group of values.
  • RANK - RANK is an analytic function that returns the rank of a value in a group of values. It is v...
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 150 users online    © 2009 psoug.org
PSOUG LOGIN
Username: 
Password: 
Forgot your password?