RANK Function - SQL
Overview
The RANK function in SQL assigns a rank to each row within a partition of a result set. If there are ties in the values, it assigns the same rank to the tied rows and skips subsequent ranks.
Example:
SELECT department_id, first_name, salary,
RANK() OVER (PARTITION BY department_id ORDER BY salary DESC)
FROM company.employees
Syntax:
SELECT column_name,
RANK() OVER (PARTITION BY partition_column[s] ORDER BY order_column[s])
FROM table_name
partition_column[s] is the column or columns that divide the result set into partitions.
order_column[s] is the column or columns that specify the order of rows within each partition.
RANK() assigns a rank to each row within the partition. Tied rows receive the same rank, and the next rank value is incremented by the number of tied rows.
Sample Data:
Before
| department_id | first_name | salary |
|---|---|---|
| 3 | Frank | 123000 |
| 2 | Jane | 135000 |
| 3 | Ashley | 115000 |
| NULL | Glenn | 115000 |
| 2 | Kelly | 125000 |
| 1 | Richard | 120000 |
| 1 | George | 105000 |
| 5 | Kyle | 200000 |
| 2 | James | 107000 |
| 1 | Gustavo | 100000 |
After
| department_id | first_name | salary |
|---|---|---|
| NULL | Glenn | 115000 |
| 1 | Richard | 120000 |
| 1 | George | 105000 |
| 1 | Gustavo | 100000 |
| 2 | Jane | 135000 |
| 2 | Kelly | 125000 |
| 2 | James | 107000 |
| 3 | Ashley | 115000 |
| 3 | Frank | 123000 |
| 5 | Kyle | 200000 |
Example: Query without the WHERE Statement
In this example, we are returning the department_id, first_name, and salary column, along with a RANK column to assign a rank to each employee’s salary within their department, ordering the salaries in descending order. If multiple employees have the same salary, they receive the same rank, and the next rank is skipped accordingly.
Example: Query with the WHERE Statement
In this example, we are filtering for only rows in department_id 1. We are returning the department_id, first_name, and salary column, along with a RANK column to assign a rank to each employee’s salary within their department, ordering the salaries in descending order. If multiple employees have the same salary, they receive the same rank, and the next rank is skipped accordingly.
