The SQL RANK() function is a window function that assigns a rank to each row within a result set based on the values in one or more columns. The rank value represents the relative position of the row within the result set, with a lower rank indicating a higher position.
Syntax
The syntax of the RANK() function is as follows:
RANK() OVER (
[PARTITION BY partition_expression, ... ]
ORDER BY sort_expression [ASC | DESC], ...
)
The PARTITION BY clause divides the result set into partitions or groups based on the specified column(s), and the ORDER BY clause specifies the column(s) by which the rows are sorted within each partition.
The RANK() function returns a numeric value for each row that represents its rank within the partition. The rank value starts at 1 for the first row in each partition, and increments by 1 for each subsequent row with the same values in the ORDER BY clause.
Example
For example, consider the following table called students:
| id | name | score |
|---|---|---|
| 1 | Alice | 90 |
| 2 | Bob | 80 |
| 3 | Carol | 90 |
| 4 | Dave | 70 |
To assign ranks to the rows based on the score column in descending order, we can use the following query:
SELECT name, score, RANK() OVER (ORDER BY score DESC) as rank FROM students;
This would produce the following result set:
| name | score | rank |
|---|---|---|
| Alice | 90 | 1 |
| Carol | 90 | 1 |
| Bob | 80 | 3 |
| Dave | 70 | 4 |
As you can see, Alice and Carol both have a rank of 1 because they have the highest score, while Bob has a rank of 3 and Dave has a rank of 4.
In summary, the SQL RANK() function is a powerful tool for assigning ranks to rows within a result set based on one or more columns. It can be used to perform a wide range of analytical tasks, such as identifying the top-performing items in a dataset, or identifying the least-performing items.