SQL Server RANK() Function

In SQL, the RANK() function is used to assign ranks to rows in a result set based on specified sort criteria in ascending or descending order. The rank results can be used to generate ranking reports, calculate percentiles, and so on. The RANK() function is one of the functions defined in the SQL:2003 standard and can be used in a variety of database management systems, including SQL Server.

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 and ORDER BY clauses are required, and ASC and DESC indicate ascending and descending order, respectively.

Usage

The RANK() function is typically used in scenarios where data needs to be ranked, such as calculating rankings of scores in a competition, sales rankings, and student grade rankings. It can be combined with other functions (such as SUM(), AVG(), etc.) to generate more summary information.

Examples

Here are two examples of the RANK() function:

Example 1

Suppose there is a table named Scores, which includes fields such as student name, subject, and score. We need to calculate the rankings of the top three students in each subject.

SELECT
    Subject,
    Name,
    Score,
    RANK() OVER (
        PARTITION BY Subject
        ORDER BY Score DESC
    ) AS Rank
FROM Scores
WHERE Rank <= 3;

After executing the above SQL statement, the following results are obtained:

The above results display the names, scores, and rankings of the top three students in each subject.

Example 2

Suppose there is a table named Scores that includes student ID and score information as follows:

Now we want to rank the students’ scores and handle ties. The RANK() function can be used to accomplish this. Here is the SQL code to do this:

SELECT ID, Score, RANK() OVER (ORDER BY Score DESC) AS Rank
FROM Scores;

After executing the above SQL statement, the following results are obtained:

From the results, we can see that the students’ scores are sorted in descending order and ties are handled appropriately. Students with the same score are assigned the same rank.

Conclusion

This article introduced the RANK() function in SqlServer, which can be used for ranking data and handling ties. The syntax of the RANK() function is relatively simple and easy to use. In practical applications, this function can be used in various ranking scenarios, such as competition score ranking, sales ranking, and so on.