RANK function is a built-in ranking function in SQL Server that returns the rank of a value in a given list.
The rank of a value is its position in the list, with the first value having a rank of 1.
The RANK function can be used in conjunction with the ORDER BY clause to sort a result set by rank.
RANK() OVER ([PARTITION BY value_exp, ... [ n ]] ORDER BY_clause)
In the event of a tie,
RANK will provide the next available number in the sequence.
When there is a tie in a set of rankings, use this function to ensure that all tied rows receive the same ranking while the row following receives the ranking it would have received without a tie. That is to say, RANK does not guarantee numerical continuity when assigning ranks.
Using RANK with OVER clause
Here is an example of how the RANK function with OVER clause can be used to rank rows in a query:
RANK() OVER(ORDER BY price, name) AS RowRank,
Using RANK with TOP
For example, the following query uses the RANK function to return the top 5 ebooks by price:
SELECT TOP 5
RANK() OVER(ORDER BY price DESC) AS RowRank,