The most frequently rank vs dense_rank used operations within SQL Server are the ranking functions, which allow us to assign a unique number to each entry based on a predetermined order. Although developers often confuse them, RANK() and DENSE_RANK() are the most widely used of these functions. Although both are useful for addressing analytical queries, they take different tacks for resolving duplicate values.
This blog post will explain the difference between RANK() and DENSE_RANK() by offering a simple example in order to make the point.
What is RANK()?
Each row in a result set is given a rank using the RANK() function. Two or more rows are assigned the same rank if their values are same. But the subsequent or next rank is skipped.
Key Point: When duplicate values are present, RANK() creates gaps in the ranking.
How does DENSE_RANK() work?
The major difference between Rank() and DENSE_RANK() are – Although it doesn’t skip ranks, the DENSE_RANK() function is comparable to RANK() function. When two or more rows share a same value, they are assigned the same rank, and the next upcoming rank is applied consecutively sequentially without any gaps.
Key Point: DENSE_RANK() implies that there are no ranking gaps between two rows if the results values are same.
Example: RANK vs DENSE_RANK
Let’s take an example of Tbl_EmployeeSales data for better understanding:
Table create syntax:
CREATE TABLE Tbl_EmployeeSales (
EmpName VARCHAR(50),
Sales INT
);
Let’s take an example of Tbl_EmployeeSales data for better understanding:
Insert Value in Table (Tbl_EmployeeSales) - Synta
INSERT INTO Tbl_EmployeeSales VALUES
('Rohit Kushwaha', 5000),
('Aman', 7000),
('Sharda', 7000),
('Anita', 6000),
('Raj', 5000),
('Vikas Gupta', 8000);Now, let’s apply both functions for view output:
SELECT
EmpName,
Sales,
RANK() OVER (ORDER BY Sales DESC) AS RankValue,
DENSE_RANK() OVER (ORDER BY Sales DESC) AS DenseRankValue
FROM Tbl_EmployeeSales;Output:
| EmployeeName | Sales | RankValue | DenseRankValue |
|---|---|---|---|
| Vikas Gupta | 8000 | 1 | 1 |
| Aman | 7000 | 2 | 2 |
| Sharda | 7000 | 2 | 2 |
| Anita | 6000 | 4 | 3 |
| Rohit Kushwaha | 5000 | 5 | 4 |
| Raj | 5000 | 5 | 4 |
Analysis of the Output
Using RANK():
- Both Aman and Sharda obtain Rank = 2 because their sales (7000) are equal.
- When the next rank jumps from 3 to 4 instead of there is a gap that exists.
Using DENSE_RANK():
- Dense Rank = 2 is still shared by Aman and Sharda.
- Without missing any numbers, afterwards rank is 3.
When Should I Use DENSE_RANK() vs. RANK()?
Even if there are any gaps between row number with same value then utilize the RANK() function when a precise ranking position is required. For real example, at athletic events, proper representation of tied positions is critical.
To provide continuous numbering without skipping, call DENSE_RANK() function in sql server data base. For example, generating category rankings or groupings with misleading gaps.
Finally, understanding the differences between the RANK() and DENSE_RANK() methods is critical part when you are working with ordered datasets in a SQL Server database.
Key Points – If the values in two or more rows are the same, RANK() permits sequence gaps, but in case of DENSE_RANK() function provides continuous ranking without interruptions between two or more rows with identical values.
By using the appropriate function, you can improve the accuracy and significance of your reports data in sql server database.
