Introduction to RANK Function
The RANK function in SQL Server is used to assign a rank to each row within a result set based on a specified column or columns. It is a window function that allows you to perform ranking operations on a set of data. The rank assigned to each row is determined by the order of the values in the specified column(s). The RANK function is commonly used in scenarios where you need to identify the top or bottom ranked records based on certain criteria.
Syntax of RANK Function
The syntax of the RANK function in SQL Server is as follows:
«`sql
RANK() OVER (PARTITION BY column1, column2,… ORDER BY column3, column4,…)
«`
Let’s break down the syntax:
– `RANK()` is the function name.
– `OVER` is a keyword that indicates the start of the window function.
– `PARTITION BY` is an optional clause that divides the result set into partitions based on one or more columns. The RANK function will assign ranks within each partition separately.
– `ORDER BY` is a mandatory clause that specifies the column(s) by which the ranking is determined. The RANK function will assign ranks based on the order of values in the specified column(s).
Example of Using RANK Function
Let’s consider a simple example to understand how the RANK function works. Suppose we have a table called «Employees» with the following data:
| EmployeeID | EmployeeName | Salary |
|————|————–|——–|
| 1 | John | 5000 |
| 2 | Jane | 6000 |
| 3 | Mark | 4000 |
| 4 | Sarah | 5500 |
| 5 | David | 4500 |
Now, let’s say we want to assign ranks to the employees based on their salaries. We can use the RANK function to achieve this:
«`sql
SELECT EmployeeID, EmployeeName, Salary, RANK() OVER (ORDER BY Salary DESC) AS Rank
FROM Employees
«`
The above query will return the following result:
| EmployeeID | EmployeeName | Salary | Rank |
|————|————–|——–|——|
| 2 | Jane | 6000 | 1 |
| 4 | Sarah | 5500 | 2 |
| 1 | John | 5000 | 3 |
| 5 | David | 4500 | 4 |
| 3 | Mark | 4000 | 5 |
As you can see, the RANK function has assigned ranks to each employee based on their salaries in descending order.
Using RANK Function with PARTITION BY Clause
The PARTITION BY clause in the RANK function allows you to divide the result set into partitions based on one or more columns. The RANK function will assign ranks within each partition separately. This can be useful when you want to rank records within specific groups.
Let’s consider an example to understand how the PARTITION BY clause works. Suppose we have a table called «Orders» with the following data:
| OrderID | CustomerID | OrderDate | TotalAmount |
|———|————|————|————-|
| 1 | 1 | 2021-01-01 | 100 |
| 2 | 1 | 2021-02-01 | 200 |
| 3 | 2 | 2021-01-01 | 150 |
| 4 | 2 | 2021-02-01 | 250 |
| 5 | 3 | 2021-01-01 | 300 |
| 6 | 3 | 2021-02-01 | 400 |
Now, let’s say we want to assign ranks to the orders based on the total amount within each customer group. We can use the RANK function with the PARTITION BY clause to achieve this:
«`sql
SELECT OrderID, CustomerID, OrderDate, TotalAmount, RANK() OVER (PARTITION BY CustomerID ORDER BY TotalAmount DESC) AS Rank
FROM Orders
«`
The above query will return the following result:
| OrderID | CustomerID | OrderDate | TotalAmount | Rank |
|———|————|————|————-|——|
| 2 | 1 | 2021-02-01 | 200 | 1 |
| 1 | 1 | 2021-01-01 | 100 | 2 |
| 4 | 2 | 2021-02-01 | 250 | 1 |
| 3 | 2 | 2021-01-01 | 150 | 2 |
| 6 | 3 | 2021-02-01 | 400 | 1 |
| 5 | 3 | 2021-01-01 | 300 | 2 |
As you can see, the RANK function has assigned ranks to the orders based on the total amount within each customer group.
Using RANK Function with ORDER BY Clause
The ORDER BY clause in the RANK function specifies the column(s) by which the ranking is determined. The RANK function will assign ranks based on the order of values in the specified column(s). By default, the RANK function assigns the same rank to rows with the same values.
Let’s consider an example to understand how the ORDER BY clause works. Suppose we have a table called «Students» with the following data:
| StudentID | StudentName | Marks |
|———–|————-|——-|
| 1 | John | 80 |
| 2 | Jane | 90 |
| 3 | Mark | 80 |
| 4 | Sarah | 95 |
| 5 | David | 85 |
Now, let’s say we want to assign ranks to the students based on their marks. We can use the RANK function with the ORDER BY clause to achieve this:
«`sql
SELECT StudentID, StudentName, Marks, RANK() OVER (ORDER BY Marks DESC) AS Rank
FROM Students
«`
The above query will return the following result:
| StudentID | StudentName | Marks | Rank |
|———–|————-|——-|——|
| 4 | Sarah | 95 | 1 |
| 2 | Jane | 90 | 2 |
| 5 | David | 85 | 3 |
| 1 | John | 80 | 4 |
| 3 | Mark | 80 | 4 |
As you can see, the RANK function has assigned ranks to the students based on their marks in descending order. Since two students (John and Mark) have the same marks, they are assigned the same rank (4) and the next rank (5) is skipped.
Using RANK Function with WHERE Clause
The RANK function can also be used in conjunction with the WHERE clause to filter the result set based on certain conditions. This allows you to rank only a subset of the records that meet the specified criteria.
Let’s consider an example to understand how the WHERE clause works with the RANK function. Suppose we have a table called «Products» with the following data:
| ProductID | ProductName | Category | Price |
|———–|————-|———-|——-|
| 1 | Laptop | Electronics | 1000 |
| 2 | Smartphone | Electronics | 800 |
| 3 | Shirt | Clothing | 50 |
| 4 | Jeans | Clothing | 70 |
| 5 | Watch | Accessories | 200 |
Now, let’s say we want to assign ranks to the products in the «Electronics» category only. We can use the RANK function with the WHERE clause to achieve this:
«`sql
SELECT ProductID, ProductName, Category, Price, RANK() OVER (ORDER BY Price DESC) AS Rank
FROM Products
WHERE Category = ‘Electronics’
«`
The above query will return the following result:
| ProductID | ProductName | Category | Price | Rank |
|———–|————-|————-|——-|——|
| 1 | Laptop | Electronics | 1000 | 1 |
| 2 | Smartphone | Electronics | 800 | 2 |
As you can see, the RANK function has assigned ranks to the products in the «Electronics» category based on their prices in descending order.
Using RANK Function with JOIN Clause
The RANK function can also be used in conjunction with the JOIN clause to combine the ranking information with other tables. This allows you to retrieve additional information related to the ranked records.
Let’s consider an example to understand how the JOIN clause works with the RANK function. Suppose we have two tables called «Customers» and «Orders» with the following data:
Customers table:
| CustomerID | CustomerName |
|————|————–|
| 1 | John |
| 2 | Jane |
| 3 | Mark |
Orders table:
| OrderID | CustomerID | OrderDate | TotalAmount |
|———|————|————|————-|
| 1 | 1 | 2021-01-01 | 100 |
| 2 | 1 | 2021-02-01 | 200 |
| 3 | 2 | 2021-01-01 | 150 |
| 4 | 2 | 2021-02-01 | 250 |
| 5 | 3 | 2021-01-01 | 300 |
| 6 | 3 | 2021-02-01 | 400 |
Now, let’s say we want to retrieve the top 3 orders for each customer along with their ranks. We can use the RANK function with the JOIN clause to achieve this:
«`sql
SELECT C.CustomerID, C.CustomerName, O.OrderID, O.OrderDate, O.TotalAmount, R.Rank
FROM Customers C
JOIN (
SELECT OrderID, CustomerID, OrderDate, TotalAmount, RANK() OVER (PARTITION BY CustomerID ORDER BY TotalAmount DESC) AS Rank
FROM Orders
) R ON C.CustomerID = R.CustomerID
WHERE R.Rank <= 3
```The above query will return the following result:| CustomerID | CustomerName | OrderID | OrderDate | TotalAmount | Rank |
|------------|--------------|---------|------------|-------------|------|
| 1 | John | 2 | 2021-02-01 | 200 | 1 |
| 1 | John | 1 | 2021-01-01 | 100 | 2 |
| 2 | Jane | 4 | 2021-02-01 | 250 | 1 |
| 2 | Jane | 3 | 2021-01-01 | 150 | 2 |
| 3 | Mark | 6 | 2021-02-01 | 400 | 1 |
| 3 | Mark | 5 | 2021-01-01 | 300 | 2 |As you can see, the RANK function has assigned ranks to the top 3 orders for each customer based on the total amount.
Conclusion
The RANK function in SQL Server is a powerful tool for performing ranking operations on a set of data. It allows you to assign ranks to rows based on a specified column or columns. By using the RANK function, you can easily identify the top or bottom ranked records based on certain criteria. Whether you need to rank records within specific groups, filter the result set based on certain conditions, or combine the ranking information with other tables, the RANK function provides a flexible and efficient solution.
