How to use RANK Function in SQL Server

by

In this SQL Server tutorial, I will explain how to use the RANK function in SQL Server, which ranks the rows in the result set.

First, I will describe the RANK() function with syntax, and then, with an example, you will understand how to use this function to assign rank to rows.

After that, you will learn how to partition the result set and rank the rows in each partition separately using the RANK() function.

Rank assigns the same rank to rows having the same values; it creates ties in the ranking, so you will also learn how to handle ranking ties.

RANK Function in SQL Server

The RANK function in SQL Server assigns a unique rank (or number) to each row within the partition of the result set.

The first row in the partition is assigned the rank of 1. If the two rows have the same value in the partition, then the same rank value is assigned to both.

The syntax is given below.

RANK() OVER (PARTITION BY partition_expression ORDER BY order_expression [ASC | DESC])

Where,

  • RNAK(): It is the RANK() function.
  • PARTITION BY: It divides the result set into a partition based on the partition_expression. Here, partition_expression is the column by which the partition is done.
  • ORDER BY: It is used to specify the order of the rows within each partition.
  • order_expression: This is the expression by which rows are ordered in each partition.
  • ASC | DESC: It defines how you want to order the rows, ASC for ascending and DESC for descending order.

Now, let’s look at an example of how to rank the rows in the result set.

For example, suppose you have a SaleTransaction table with columns TransactionID, SaleAmount and TaxRate, as shown below.

RANK Function in SQL Server with SalesTransaction Table

Suppose you must rank each transaction based on the sale amount; you can use RANK() OVER, as shown in the query below.

SELECT TransactionID, SaleAmount,
	RANK() OVER (ORDER BY SaleAmount DESC) AS TransactionRank
FROM Sales.SalesTransactions;
RANK Function in SQL Server

Each transaction ID is ranked in descending order from 1 to 10 from the output.

In the query part RANK() OVER (ORDER BY SaleAmount DESC), the RANK() is a window function that assigns a unique rank to each row based on the specified ordering.

OVER (ORDER BY SaleAmount DESC) defines the window or frame within which the RANK function operates. Here, it orders the rows by the SaleAmount column in descending order using the DESC keyword.

But remember that a partition is not created here; this is a simple ranking.

RANK Function in SQL Server with Partition By Clause

In the above section, rows are ranked without partition, so here, you will understand how to use the PARTITION BY clause and rank the rows based on the partition.

For example, you have an Employees table with columns EmployeeID, Name, Salary, and Department.

RANK Function in SQL Server with Partition By Clause Table Employees

Now, you have to rank employees within each department based on their salary, so here, you can use the PARTITION BY clause to partition the employees or rows based on the department column and apply a RANK() function to assign the rank based on the salary.

For that, you can use the query below.

SELECT
	Name,
	Salary,
	Department,
	RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS DepartmentSalaryRank
FROM
	Employees;
RANK Function in SQL Server with Partition By Clause

The output shows that the other result set is divided into partitions based on the Department column. If you look closely at each partition, the rank is assigned from 1, but as the next partition comes, the ranking again starts from 1.

For example, Look at the Finance partition where ranks are assigned from 1 to 4 to rows, respectively.

Then, the next partition is HR, where the ranking starts from 1 and ends at 2. The same pattern is followed for all other partitions in the result set.

Now understand the query part, RANK() OVER (PARTITION BY Department ORDER BY Salary DESC). Here, the RANK() function is applied over the salary column to rank the employees based on their salary.

Then PARTITION BY Department divides the result set into partitions based on the different departments, each containing rows related to that department. After that, each row within the partition is ordered by the employees’ salary in descending order.

As a result, it returns a result set containing partitions based on the Department column, and with each partition, rows are assigned a rank from 1 to the number of rows in each partition.

This is how to use the PARTITION BY clause with the RANK() OVER function to partition the result set based on the specific column.

Handling Ties using RANK Function in SQL Server

As you can see in the Employees table, some of the employees have the same salary, and if you rank that employee, then the RANK() function will assign the same rank value.

For example, execute the query below to assign a unique rank to each employee without partition by clause.

SELECT
	Name,
	Salary,
	Department,
	RANK() OVER (ORDER BY Salary DESC) AS SalaryRank
FROM
	Employees;
Handling Ties using RANK Function in SQL Server in a Employees Table

If you look at the query output, the employees with the same salary have been assigned the same rank value. But as you can see, there is a gap in the ranking; for example, after the employee Emma Kind, the ranking begins from 12 instead of 11.

Also, after the employee Daniel Thomas, the ranking begins from 17 instead of 15.

To resolve this issue, another function called the DENSE_RANK() function exists. So, use the query below.

Handling Ties using RANK Function in SQL Server

Now, if you look at the SalaryRank column, there is no gap in ranking value; for example, look at ranking employee Daniel Thomas; after this, the ranking begins from 13.

This is how to handle the ties in the ranking using the DENSE_RANK() function in SQL Server.

Conclusion

You learned how to assign the unique rank to each row within the result set using the RANK function in SQL Server.

You also learned to rank the rows in a specific order using the ORDER BY clause within the RANK() OVER() function. You also learned how to partition the result set based on the specific column and rank each row within that partition.

Additionally, you learned how to handle the ties in ranking using the DENSE_RANK() function in the SQL Server.

You may like to read: