Rank Function in SQL
RANK Function in SQL
The RANK() function is a window function that assigns a rank to each row within a partition of a result set, based on the order specified. Rows with the same values receive the same rank, but the next rank is skipped (i.e., it creates gaps).
Syntax:
RANK() OVER ( [PARTITION BY partition_expression] ORDER BY sort_expression [ASC|DESC])PARTITION BY(optional): Divides the result set into partitions to rank within each partition.ORDER BY: Specifies the order in which to rank rows.
Example:
Assuming a table employees with columns: employee_id, department, salary.
Rank employees by salary within each department:
SELECT employee_id, department, salary, RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS salary_rankFROM employees;Example Output:
| employee_id | department | salary | salary_rank |
|---|---|---|---|
| 4 | IT | 70000 | 1 |
| 5 | IT | 70000 | 1 |
| 2 | HR | 60000 | 1 |
| 6 | HR | 55000 | 2 |
| 3 | Sales | 55000 | 1 |
| 1 | Sales | 50000 | 2 |
Notice that if two employees have the same salary, they share the same rank, and the next rank skips accordingly.
Difference from DENSE_RANK():
RANK()leaves gaps in ranking when ties occur.DENSE_RANK()does not leave gaps.
Supported In:
SQL Server
PostgreSQL
Oracle
MySQL (8.0+)
Want me to provide examples comparing RANK() vs DENSE_RANK()?
