--- title: "What is the difference between RANK() and DENSE_RANK() functions in SQL Server ." description: "What is the difference between RANK() and DENSE_RANK() functions in SQL Server ." author: "Anonymous User" published: 2015-12-09 updated: 2015-12-09 canonical: https://www.mindstick.com/forum/33707/what-is-the-difference-between-rank-and-dense_rank-functions-in-sql-server category: "database" tags: ["sql server", "sql", "sql server 2008", "sql server 2012"] reading_time: 3 minutes --- # What is the difference between RANK() and DENSE_RANK() functions in SQL Server . I want to know What is the [difference](https://www.mindstick.com/articles/157114/good-news-or-bad-news-and-the-difference-is) between [RANK](https://www.mindstick.com/forum/34366/difference-between-rank-and-dense_rank-in-sql-server)() and DENSE_RANK() [functions in SQL](https://www.mindstick.com/forum/158913/explain-the-purpose-of-the-max-and-min-functions-in-sql-and-provide-examples) [Server](https://www.mindstick.com/articles/43769/what-is-serverless-architecture-is-it-worth-switching-over) .[please tell me](https://www.mindstick.com/forum/33900/please-tell-me-what-are-the-technique-to-content-optimization) with an example. ## Replies ### Reply by Anonymous User **RANK() FUNCTION** The RANK() function will return the ranking of a set of values within a given partition. What exactly do we mean by ranking? Well, suppose we have a set of some values like {3000, 2000, 5000, 1000}. If we arrange those values in order from highest to lowest in descending order, then it would look like {5000, 3000, 2000, 1000}. And if we apply the RANK() function against that set of values, then it will assign each value in the set a “rank”, which is just a numeric integer value starting from 1 that indicates the value’s rank in comparison to the other values in the set. So, the rank for the set {5000, 3000, 2000, 1000} would be {1, 2, 3, 4} – where each rank corresponds to the value in the original set in the same position – so the ranking of 1 is for 5000, 2 is for 3000, etc. What happens to the rank of a value that comes after a tie? Well, the very next rank would actually skip when using the RANK() function leading to non-consecutive ranks. If that’s confusing, then consider our example set of {3000, 1000, 2000, 3000, 5000, 1000}. Let’s say this set is then sorted in ascending order – so it looks like {1000, 1000, 2000, 3000, 3000, 5000}. The ranking of that set will then be {1, 1, 3, 4, 4, 6}. Note that the “2000” value is assigned a ranking of 3, because a ranking is skipped due to the tie between the two “1000” values that come before. If there were 3 “1000” values in the set, then the rankings would look like {1, 1, 1, 4, 5, 5, 7}, because there is a tie among 3 values, the rank will also skip to a value of 4. ``` SELECT Emp_ID,Emp_Name,Emp_Sal, rank() over (ORDER BY Emp_Sal DESC) AS Ranking FROM Employee; ``` ![What is the difference between RANK() and DENSE_RANK() functions in SQL Server .](https://www.mindstick.com/mindstickforums/0f8c930d-328b-4c13-8f59-f472204713bd/images/65451246-40ca-4897-b26e-ae063573f24d.png) **DENSE_RANK()** With the DENSE_RANK() function, if there is a tie then none of the ranks will be skipped. This means that the ranks will remain in consecutive order. Let’s take a look at our sample set of values again: {1000, 1000, 2000, 3000, 3000, 5000}. If we apply the DENSE_RANK function against this set of values, then we will end up with these rankings: {1,1,2,3,3,4}. Note that even when there is a tie, the next value will have a ranking that is the next consecutive integer value – and no value will be skipped. And that is why it is called a dense rank – because all the ranking values are used without skipping – maintaining the “density”, or tightness of rank values. ``` SELECT Emp_ID,Emp_Name,Emp_Sal,dense_rank() over (ORDER BY Emp_Sal DESC) AS DenseRank FROM Employee; ``` ![What is the difference between RANK() and DENSE_RANK() functions in SQL Server .](https://www.mindstick.com/mindstickforums/0f8c930d-328b-4c13-8f59-f472204713bd/images/5a954cc4-7e30-492d-a5ca-42fd12a20ee2.png) ## difference between DENSE_RANK() and RANK() The one and only difference between the DENSE_RANK() and RANK() [functions](https://www.mindstick.com/forum/160140/explain-the-role-of-functions-as-a-service-faas-in-serverless-computing) is the fact that RANK() will assign non-consecutive ranks to the values in a set in the case of a tie, which means that with RANK() there will be gaps between the integer values when there is a tie. But the DENSE_RANK() will assign consecutive ranks to the values in the case of a tie, so there will be no gaps between the integer values in the case of a tie. --- Original Source: https://www.mindstick.com/forum/33707/what-is-the-difference-between-rank-and-dense_rank-functions-in-sql-server Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.