RANK In Oracle/ PL-SQL

RANK Function:

• Returns the rank of a value in a group of values.
• A built in analytic function which is used to rank a record within a group of rows. 
• Return type is number and serves for both aggregate and analytic purpose in SQL.
• Rows with equal values for the ranking criteria receive the same rank.
• Ties are assigned the same rank, with the next ranking(s) skipped. So, if we have 3 items at rank 2, the next rank listed would be ranked 5.

Examples:

1.  Query to return the rank for a $50000 salary(Single Column RANK)

SELECT RANK(50000) WITHIN GROUP
(ORDER BY salary DESC NULLS LAST) SAL_RANK
FROM employees;

2.  Query to return the rank for an employee with a salary of $50,000 and a commission of 10%$(Multiple Column RANK)

SELECT RANK(.10,50000) WITHIN GROUP
(ORDER BY commission_pct, salary) RANK
FROM employees;

3.  Query to find the employee with the nth highest salary

SELECT *
FROM (
  SELECT employee_id, last_name, salary,
  RANK() OVER (ORDER BY salary DESC) EMPRANK
  FROM employees)
WHERE emprank = n;

4. Query to rank the employees  in department 60 based on their salaries. 
Identical salary values receive the same rank and cause nonconsecutive ranks.

SELECT department_id, last_name, salary,

    RANK() OVER (PARTITION BY department_id ORDER BY salary) RANK

  FROM employees WHERE department_id = 60

  ORDER BY RANK, last_name;

Posted in: Oracle Advanced Concepts,Oracle Notebook,Technical