Rank Functions in SQL server

Introduction

Rank functions in SQL are used to assign a rank to each row within a partition of a result set. These functions are useful for a variety of applications, such as finding the top N records in a group, generating unique rankings for rows, and more. The four primary rank functions in SQL are,

RANK()

Assigns a unique rank to each distinct row within the partition. If two rows have the same rank, the next rank value is skipped.

Script

-- Table creation
CREATE TABLE Sales (
    SalesID INT PRIMARY KEY,
    SalesPerson VARCHAR(50),
    SalesAmount DECIMAL(10, 2)
);

-- Insert Script

-- Data insertion
INSERT INTO Sales (SalesID, SalesPerson, SalesAmount) VALUES
    (1, 'Devesh', 5090.00),
    (2, 'Manju', 3060.00),
    (3, 'Hemant', 3100.00),
    (4, 'Shiv', 3060.00),
    (5, 'Rajesh', 15600.00),
    (6, 'Pankaj', 1200.00);

-- Rank Query

-- Using RANK()
SELECT
    SalesPerson,
    SalesAmount,
    RANK() OVER (ORDER BY SalesAmount DESC) AS Rank
FROM Sales;

-- Result

Explanation

In this example, we are trying to get a salesperson who has max sales, and based on that, we are providing a Rank.

Shiv and Madhu have the same Sales amount, so both have Rank 4.

Key Point

We can see Pankaj should be 5 but Rank does not maintain sequence, we can fix this in dense rank.

DENSE_RANK(): Similar to RANK(), but does not skip rank values in the case of ties.

Having the same example and script above now I am going to add the Dense rank Column.

-- Using RANK() and DENSE_RANK()
SELECT
    SalesPerson,
    SalesAmount,
    RANK() OVER (ORDER BY SalesAmount DESC) AS Rank,
    DENSE_RANK() OVER (ORDER BY SalesAmount DESC) AS DenseRank
FROM Sales;

Result

Explanation

We can see that the Column Dense Rank maintains the sequence.

ROW_NUMBER()

Assigns a unique sequential integer to rows within the partition of a result set, starting at 1.

This simply adds a sequence number to each row.

New script

SELECT
    SalesPerson,
    SalesAmount,
    RANK() OVER (ORDER BY SalesAmount DESC) AS Rank,
    DENSE_RANK() OVER (ORDER BY SalesAmount DESC) AS DenseRank,
    ROW_NUMBER() OVER (ORDER BY SalesAmount DESC) AS RowNumber
FROM Sales;

Result

More Examples

Find Nth Highest salary with Rank()

We are trying to find the 4th Highest salary.

Script

-- Table creation
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName VARCHAR(50),
    Salary DECIMAL(10, 2)
);

-- Data insertion
INSERT INTO Employees (EmployeeID, EmployeeName, Salary) VALUES
    (1, 'Ajay', 6000.00),
    (2, 'Devesh', 8000.00),
    (3, 'TK Gupta', 8000.00),
    (4, 'Mahesh', 5000.00),
    (5, 'Rakesh', 9000.00),
    (6, 'Sanjay', 7000.00);

-- Find the 4th highest salary using RANK()
WITH RankedSalaries AS (
    SELECT
        EmployeeName,
        Salary,
        RANK() OVER (ORDER BY Salary DESC) AS Rank
    FROM Employees
)
SELECT
    EmployeeName,
    Salary
FROM RankedSalaries
WHERE Rank = 4;

Ranking Employees by Salary Within Departments.

In a company, you might want to rank employees by their salary within each department to identify the top earners in each department.

Script

-- Table creation
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName VARCHAR(50),
    Department VARCHAR(50),
    Salary DECIMAL(10, 2)
);

-- Data insertion
INSERT INTO Employees (EmployeeID, EmployeeName, Department, Salary) VALUES
    (1, 'Sanjay', 'HR', 6000.00),
    (2, 'Devesh', 'Finance', 8000.00),
    (3, 'Mahesh Kumar', 'Finance', 8000.00),
    (4, 'Hitesh', 'HR', 5000.00),
    (5, 'Himanshu', 'IT', 9000.00),
    (6, 'Honey', 'IT', 9000.00),
    (7, 'Kapil', 'IT', 8500.00);

-- Ranking employees by salary within each department
SELECT
    EmployeeName,
    Department,
    Salary,
    RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS Rank
FROM Employees;

We have made a partition based on the department and got the desired result.