Know Your Rank

Rank calculates the rank of a value in a group of values. Rows with equal values receive equal ranking. It computes the rank of each row returned from a query with respect to the other rows returned by the query based on the values of value expression in the order_by_clause. In a simple English, this function is commonly used in school to calculate the rank of each student in class. You may enter the mark of a student to find out his position or to calculate the position of each student in a class. Examples are the best way to learn and understand rank. As always, I am going to use our standard employee to demo examples.

Demo Employee Table Data:

SELECT employee_id,
  first_name,
  last_name,
  salary,
  Department_Id
FROM scott.employee;

Output:

Example 1: This example calculates the rank of salary 2400 in employee table when the salary is ordered DESC.

SELECT rank(2400) within GROUP 
(ORDER BY salary DESC) AS "Rank of 2400"
FROM scott.employee;

Output:

Example 2: This example calculates the rank of each salary from employee table in a desc order.

SELECT employee_id,
  first_name,
  last_name,
  salary,
  RANK() OVER(ORDER by salary  DESC) "Rank"
FROM scott.employee;

Output:

Example 3: This examples calcualte the rank of each salary from employee table for each dept in a desc order.

SELECT employee_id,
  first_name,
  last_name,
  salary,
  RANK() OVER(partition BY department_id ORDER by salary DESC) "Rank"
FROM scott.employee;

Output:

After following these examples, you know the exact application of Rank Function. This is mostly used during reporting or dashboard application. Now, you can calculate your rank at work or in school.