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...