RANK() Function in SQL Server

The RANK() function in SQL Server is used to assign a rank to each row within a result set based on the values in one or more columns. The rank is determined by the order of the values in the specified column(s).

Syntax:

RANK() OVER ( [PARTITION BY partition_expression, ... ] ORDER BY sort_expression [ASC | DESC], ... )

Explanation: - The PARTITION BY clause is optional and is used to divide the result set into partitions based on the values in one or more columns. The rank is assigned within each partition separately. - The ORDER BY clause is mandatory and is used to specify the column(s) based on which the rank is determined. The ASC keyword is used to sort the values in ascending order (default), and the DESC keyword is used to sort the values in descending order. - The RANK() function returns an integer value representing the rank of each row within the partition. Rows with the same values in the specified column(s) will receive the same rank, and the next rank will be skipped.

Example: Consider the following table named students:

To assign a rank to each student based on their score, we can use the following query:

SELECT name, score, RANK() OVER (ORDER BY score DESC) AS rank
FROM students

This will produce the following result:

In this example, we used the ORDER BY clause to sort the students by their score in descending order. The RANK() function then assigned a rank to each student based on their score, with Bob and David receiving the same rank of 1 because they have the highest score, and Emily receiving a rank of 3 because she has the next highest score.