Ranking Functions in SQL Server (Type of Functions in SQL Server - Part 2)

Ranking Functions in SQL Server

Ranking functions are used to assign a rank or numbering to rows within a result set. These functions are commonly used with the OVER() clause to define the ranking criteria.

Types of Ranking Functions

1. ROW_NUMBER()
2. RANK()
3. DENSE_RANK()
4. NTILE()

1. ROW_NUMBER()

• Assigns a unique number to each row within a partition or result set based on the specified order.
• No duplicate ranks, even if there are ties in the ranking criteria.

Syntax:

sql

ROW_NUMBER() OVER (PARTITION BY column_name ORDER BY column_name)

Example:

sql

SELECT EmployeeID, Name, Department,

????? ?ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS RowNum

FROM Employees;

• This assigns a unique rank to employees in each department based on salary.

2. RANK()

• Assigns a rank to rows based on the order specified.
• Generates the same rank for duplicate values, but the next rank is skipped for ties.

Syntax:

sql

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

Example:

sql

SELECT EmployeeID, Name, Department,

?????? RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS Rank

FROM Employees;

• Employees with the same salary in a department get the same rank, and the next rank skips numbers for ties.

3. DENSE_RANK()

• Similar to RANK(), but does not skip ranks for ties.
• Ensures consecutive ranking numbers, even with duplicate values.

Syntax:

sql

DENSE_RANK() OVER (PARTITION BY column_name ORDER BY column_name)

Example:

sql

SELECT EmployeeID, Name, Department,

?????? DENSE_RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS DenseRank

FROM Employees;

• Employees with the same salary in a department get the same rank, but the next rank continues without skipping.

4. NTILE()

• Divides the result set into a specified number of roughly equal parts (tiles).
• Each row is assigned a tile number.

Syntax:

sql

NTILE(number_of_tiles) OVER (PARTITION BY column_name ORDER BY column_name)

Example:

sql

SELECT EmployeeID, Name, Department,

?????? NTILE(4) OVER (ORDER BY Salary DESC) AS Tile

FROM Employees;

• Divides the result set into 4 groups, with each row assigned to a group based on salary.

Comparison of Ranking Functions

Practical Use Cases

1. ROW_NUMBER(): Paginating results, e.g., displaying rows 21–40 in a large dataset.
2. RANK(): Ranking players in a game where scores may tie.
3. DENSE_RANK(): Assigning ranks to items in a competition without gaps in ranking.
4. NTILE(): Distributing employees into performance quartiles.

Pagination with ROW_NUMBER()

sql

WITH CTE AS (

??? SELECT ROW_NUMBER() OVER (ORDER BY Salary DESC) AS RowNum, *

??? FROM Employees

)

SELECT * FROM CTE

WHERE RowNum BETWEEN 11 AND 20;

• Returns rows 11–20 for pagination.

Key Notes

• Use the OVER() clause with the PARTITION BY and ORDER BY options to define ranking logic.
• Ranking functions do not modify the data; they provide metadata for each row.
• Combining these functions with filtering, grouping, or partitioning makes them powerful for analytics.

?https://handbookofsuresh.blogspot.com/2025/01/ranking-functions-in-sql-server-type-of.html