![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.