# 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:

Subject | Name | Score | Rank |
---|---|---|---|

Math | John | 98 | 1 |

Math | Tom | 95 | 2 |

Math | Jack | 92 | 3 |

English | Mary | 89 | 1 |

English | Ann | 87 | 2 |

English | Bob | 84 | 3 |

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:

ID | Score |
---|---|

1 | 80 |

2 | 95 |

3 | 75 |

4 | 85 |

5 | 90 |

6 | 75 |

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:

ID | Score | Rank |
---|---|---|

2 | 95 | 1 |

5 | 90 | 2 |

4 | 85 | 3 |

1 | 80 | 4 |

3 | 75 | 5 |

6 | 75 | 5 |

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.