SQL RANK Function
SQL Rank function, as the name suggests, is used to define ranks for data elements. SQL Rank applies a sequentially increasing number to each row in result table. When two rows have the same value, they will be given the same rank. SQL RANK() will then skip values to ensure that the rank assigned to a given row is always one greater than the number of rows that are ranked lower.
SQL Rank can be used in two ways:
AGGREGATE RANK: used to find rank of an item in a list of items
Example 1:
The following query returns rank of an employee receiving salary amount of $2500 according to salary amounts in employee table.
SELECT RANK(2500) WITHIN GROUP (ORDER BY salary DESC) "Rank" FROM employee;
Output:
ANALYTIC RANK: used to define ranks for list of items.
Example 2:
The following defines rank for each employee in employee table according to salary amount they receive.
SELECT dept_no, first_name, salary, RANK () OVER (ORDER BY salary DESC) "Rank" FROM employee;
Output:
Example 3:
The following query is same as above except that it finds sql rank of employees in each department
SELECT dept_no, first_name, salary,
RANK() OVER (PARTITION BY dept_no
ORDER BY salary DESC) "Rank"
FROM employee;
Output:
