Mastering T-SQL Rank Functions

SQL Rank

SQL window functions are powerful tools used for advanced data analysis and manipulation. In T-SQL, there are four main window functions: ROW_NUMBER, RANK, DENSE_RANK, and NTILE. These functions help organize, order, and rank data within result sets. Let's take a closer look at each function, understanding what they do and how they're used in practice.

We will consider the below table to understand the concepts.

CREATE TABLE StudentMarks (
    StudentID INT,
    Subject VARCHAR(50),
    Marks INT
);

Adding a few sample records in the above table.

Student ID	Subject	Marks
1	Mathematics	90
2	Science	85
3	History	75
4	Mathematics	90

1. ROW_NUMBER

ROW_NUMBER is a window function that assigns a unique sequential number to each row within a partition of a result set, based on the specified ordering. It generates a new number for each row, without any gaps, starting from 1.

Use Cases: ROW_NUMBER is commonly used to implement pagination, identify duplicates, or filter top N rows within a partition.

Example

SELECT *,
       ROW_NUMBER() OVER (ORDER BY Marks DESC) AS RowNumber
FROM StudentMarks;

It will return the output.

StudentID	Subject	Marks	RowNumber
4	Mathematics	90	1
1	Mathematics	90	2
2	Science	85	3
3	History	75	4

2. RANK

RANK is another window function that assigns a unique rank to each row within a partition of a result set, based on the specified ordering. It assigns the same rank to rows with equal values, leaving gaps in the ranking sequence as needed.

Use Cases: RANK is often used in scenarios where ties in ranking are allowed, such as ranking students by exam scores.

Example

SELECT *,
       RANK() OVER (ORDER BY Marks DESC) AS Rank
FROM StudentMarks;

It will return the output.

StudentID	Subject	Marks	Rank
4	mathematics	90	1
1	Mathematics	90	1
2	Science	85	3
3	History	75	4

3. DENSE_RANK

DENSE_RANK is similar to RANK but differs in that it assigns consecutive ranks to rows with equal values, without leaving gaps in the ranking sequence. It ensures that ranks are assigned in a continuous, sequential manner.

Use Cases: DENSE_RANK is preferred when consecutive ranking without gaps is desired, such as ranking products by sales performance.

Example

SELECT *,
       DENSE_RANK() OVER (ORDER BY Marks DESC) AS DenseRank
FROM StudentMarks;

It will return the output.

StudentID	Subject	Marks	DenseRank
4	Mathematics	90	1
1	Mathematics	90	1
2	Science	85	2
3	History	75	3

4. NTILE

NTILE is a window function that divides the result set into a specified number of roughly equal-sized buckets or partitions, assigning each row to one of these buckets. The function ensures that the size difference between buckets is minimized.

Use Cases: NTILE is commonly used for data segmentation and percentile calculations, such as dividing customers into groups based on their income.

Example

SELECT *,
       NTILE(4) OVER (PARTITION BY SUBJECT ORDER BY Marks DESC) AS Student_Group
FROM StudentMarks;

It will return the output.

studentID	Subject	Marks	Student_Group
4	Mathematics	90	1
1	Mathematics	90	2
2	Science	85	1
3	History	75	1

Conclusion

Window functions such as ROW_NUMBER, RANK, DENSE_RANK, and NTILE empower developers and analysts with powerful tools for data analysis, ranking, and partitioning within SQL Server. By leveraging these functions effectively, users can gain deeper insights into their data, perform complex analytical tasks, and extract valuable information from their databases. Understanding the nuances and capabilities of each window function enables SQL practitioners to harness the full potential of T-SQL for advanced data manipulation and analysis tasks.