Rank in Microsoft SQL Server

Hello Kiran,

In Microsoft SQL Server, RANK is a in-built function used to assign a unique rank to rows within a partition of data based on the values of one or more columns.

Syntax:

RANK() OVER (PARTITION BY column_name ORDER BY column_name)

Two key point about rank function :

1. PARTITION BY: Divides the data into groups.
2. ORDER BY: Specifies the order to rank rows.

Lets take an example :

To identify the top-performing employee using the RANK function, you can rank employees based on their performance metrics and select the top performer from the entire list.

WITH RankedEmployees AS (
    SELECT
        EmployeeID,
        Name,
        Department,
        TotalSales,
        RANK() OVER (ORDER BY TotalSales DESC) AS Rank
    FROM Employees
)
SELECT 
    EmployeeID,
    Name,
    Department,
    TotalSales
FROM RankedEmployees
WHERE Rank = 1;

When to Use RANK for Analytics:

1. Identifying Top Performers: Find the top-ranking items (e.g., employees, products, etc.) based on performance metrics.
2. Handling Ties: Helps manage tied rankings while skipping numbers in sequential order.
3. Trend Analysis: Use ranks to identify patterns or trends over partitions of data.

Rank()

The RANK() function is a powerful window function in SQL Server used to assign a rank to each row within a partition of a result set.

Syntax:-

RANK() OVER (

   [PARTITION BY expression, ]

   ORDER BY expression (ASC | DESC) );

Example:-  

   CREATE TABLE Alphabates (Name VARCHAR(10) );

INSERT INTO Alphabates (Name)

VALUES ('A'), ('B'), ('B'), ('C'), ('C'), ('D'), ('E');

SELECT * FROM Alphabates;

Result

Name

A

B

B

C

C

D

E

In this example, we will use RANK() to assign ranks to the rows in the result set of the Alphabates table.

Query:

SELECT Name,

RANK () OVER (

ORDER BY Name

) AS Rank_no

FROM Alphabates;

Name    Rank_no

A            1

B            2

B            2

C            4

C            4

D            6

E            7

Function             Handles Duplicates         Gaps in Ranking      Unique Row Numbers

RANK()                         Yes                                         Yes                     No

The RANK() function in SQL Server is a window function used to assign a rank to rows within a result set, based on the order of a specified column or columns. The ranks start from 1 for each partition or the entire dataset. If there are ties (rows with the same value), they get the same rank, and the next rank is skipped.

RANK() is helpful in analytics for tasks like:

• Finding top-performing products or employees.
• Identifying trends by ranking sales over months.
• Detecting duplicates or outliers.

Syntax

RANK() OVER (PARTITION BY column_name ORDER BY column_name ASC|DESC)

• PARTITION BY: Divides the data into groups (optional).
• ORDER BY: Specifies the column(s) to sort the rows for ranking.

The RANK() function in SQL Server is used to assign a rank number to rows in a result set based on the order of a specific column. It’s useful when you want to find the position of a row in a list, such as ranking employees by salary or products by sales.

RANK() Working:

1. It assigns the same rank to rows with the same value in the column you're ordering by.
2. The next rank skips numbers if there are ties.

Usecases:

• Find Top Performers: Identify the best employees, products, or salespeople.
• Group Analysis: Rank items within specific groups, like regions or departments.
• Handle Ties: It naturally handles rows with the same values by assigning the same rank and skipping numbers after ties.

The RANK() function in SQL Server is used to give a rank to each row in a group of a result. It assigns ranks starting from 1 for the first row and then increases the rank for each row based on the order you specify. If two or more rows have the same values, they will get the same rank. However, the next rank will be skipped. For example, if two rows are ranked 1, the next rank will be 3.
Syntax:

RANK() OVER (PARTITION BY  ORDER BY  ASC/DESC)

PARTITION BY: Splits the result into smaller groups where the RANK() function is applied. If you don’t use it, the entire result set will be treated as one group.
ORDER BY: Decides the order in which ranks are given inside each group.

For example we have a sales table where we want to rank employees based on their sales figures:

SELECT 
    EmployeeID,
    SalesAmount,
    RANK() OVER (ORDER BY SalesAmount DESC) AS SalesRank
FROM Sales
ORDER BY SalesRank;

The above query ranks employees based on their SalesAmount in descending order, assigning the same rank to employees with identical sales amounts. In cases of ties, the next rank number is skipped. For example, if two employees share rank 1, the following rank will be 3.

RANK() is useful in analytics for ranking products, employees, or customers based on sales or performance to highlight top performers. 
It helps in segmenting data by regions, teams, or departments and ranking items within each group. It can be combined with filters to identify the top performers, such as the top 5 customers by sales in each group.

For more details check below links:
https://www.geeksforgeeks.org/rank-function-in-sql-server/
https://www.sqlshack.com/overview-of-sql-rank-functions/
https://stackoverflow.com/questions/12739208/how-to-use-rank-in-sql-server
https://www.sqlservertutorial.net/sql-server-window-functions/sql-server-rank-function/