In SQL, the RANK() function is an analytical function used to assign a rank to each row in a result set based on the values of one or more columns. The rank indicates the relative position of each row within the ordered partition of the result set.
Syntax
RANK() OVER (PARTITION BY partition_expression ORDER BY order_expression [ASC|DESC])
partition_expression: Optional. Divides the result set into partitions. The RANK function operates independently within each partition.order_expression: Specifies the column or columns by which the rows are ordered. Rows with the same values in the order expression are assigned the same rank.ASC or DESC: Optional. Specifies the sort order for the order expression. Default is ASC (ascending).
Example
Consider a table employees with columns employee_id and salary.
SELECT employee_id, salary,
RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;
This query assigns a rank to each employee based on their salary, with the highest salary receiving the lowest rank.
Output
| employee_id | salary | salary_rank |
|-------------|--------|-------------|
| 101 | 50000 | 1 |
| 102 | 45000 | 2 |
| 103 | 42000 | 3 |
| 104 | 42000 | 3 |
| 105 | 38000 | 5 |
Usage
- Top N Queries: Retrieve the top N rows based on a specified criterion, such as top 10 highest salaries.
- Ranking Results: Rank rows based on various factors, such as sales performance, exam scores, or customer satisfaction ratings.
- Identifying Ties: Handle ties in ranking by using additional criteria or adjusting the ordering.
Considerations
- Partitioning: Use partitioning to rank subsets of data separately.
- Ties: Ties in ranking may affect subsequent ranks, especially in dense rank scenarios.
- Performance: Analytical functions can impact query performance, especially on large datasets.
The RANK() function in SQL is a powerful tool for assigning ranks to rows based on specified criteria. Whether you're identifying top performers, analyzing sales data, or ranking exam scores, understanding how to use RANK() effectively can provide valuable insights into your data.
