What is the difference between the RANK() and DENSE_RANK() functions in both SQL Server and Oracle? Also provide examples.

First, let’s go over the behavior of the RANK() function, and then we will go over the DENSE_RANK() function. Note that in both SQL Server and Oracle, the behavior of the RANK() and DENSE_RANK() functions is the same – so this discussion applies to both relational databases.




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 {300, 200, 500, 100}. If we arrange those values in order from highest to lowest in descending order, then it would look like {500, 300, 200, 100}. 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 {500, 300, 200, 100} 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 500, 2 is for 300, etc.

Ties and the RANK() function

What happens if two or more values within a set of values are exactly the same? So, let’s say we have a set like this: {300, 100, 200, 300, 500, 100}. You can see that both “300” and “100” are repeated values within the set. When two or more values within a set have the same value then that is called a “tie”. So, what exactly does the RANK() function do in this scenario?

If there is a tie, then all the values that are “tied” will share the same rank. So, in our example set of {300, 100, 200, 300, 500, 100} the two values of 100 will share a rank, and so will the two values of 300.

The ranking after a tie will be skipped with RANK() function




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 {300, 100, 200, 300, 500, 100}. Let’s say this set is then sorted in ascending order – so it looks like {100, 100, 200, 300, 300, 500}. The ranking of that set will then be {1, 1, 3, 4, 4, 6}. Note that the “200” value is assigned a ranking of 3, because a ranking is skipped due to the tie between the two “100” values that come before. If there were 3 “100” 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.

Ties and the DENSE_RANK() function

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: {100, 100, 200, 300, 300, 500}. 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.

The difference between DENSE_RANK() and RANK()

The one and only difference between the DENSE_RANK() and RANK() functions 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.

RANK() and DENSE_RANK() without ORDER BY

Both RANK() and DENSE_RANK() can not be used without an ORDER BY statement. This is because it simply will not make sense to apply either of those functions to an unordered set of values. Think about it – if we try to rank a set of unordered values then how will the SQL processor determine whether to give the highest value a rank of 1 or the lowest value a rank of 1?

Example of RANK() in Oracle and SQL Server

The syntax for RANK() is actually the same in both Oracle and SQL Server. Here’s an example table called Employee to help illustrate how RANK() would work:

Employee
Employee_ID Salary
3 200
4 800
9 200
12 100

Note in the table above that there are two entries with the value of 200. Now, if we want to sort the values in the Employee table by the Salary in descending order, and also retrieve a ranking for each value, then we can write the following SQL in both SQL Server and Oracle:

SELECT Employee_ID, Salary, 
rank() over (ORDER BY Salary DESC) AS Ranking
FROM Employee;

And if we run the SQL above, it will return this data:

Employee_ID  Salary   Ranking
4            800      1
3            200      2
9            200      2
12           100      4

Note that in the results, the row with a Salary of 100 is given a ranking of 4, skipping the ranking of 3 entirely because of the tie between the two rows that have a salary of 200. And that is of course due to the fact that the RANK() function will skip numbers when there is a tie.

Example of DENSE_RANK() in Oracle and SQL Server

Now, let’s write some SQL that will use the dense_rank() function in SQL Server and Oracle to find the rankings:

SELECT Employee_ID, Salary, 
dense_rank() over (ORDER BY Salary DESC) AS DenseRank
FROM Employee;

Running the SQL above will return these results:

Employee_ID  Salary   DenseRank
4            800      1
3            200      2
9            200      2
12           100      3


Note that the ranking of the 4th row (with a salary of 100) is now 3 and not 4. This is because of the fact that the dense_rank() function will maintain consecutive values of the ranking, and will not skip values like the rank() function.

Hiring? Job Hunting? Post a JOB or your RESUME on our JOB BOARD >>

Subscribe to our newsletter for more free interview questions.

  • I think even oracle site has also not explained in such a nice and simple way. It would be great if you can include some more example queries like where we use partition by instead of order by

  • Anil

    This is very simple explanation.

    Thanks

  • Ram

    Mind blowing explanation. Explanation is v v simple and elaborate.

  • Ramakrishna.Bala

    IF a complete reference for performance tunning is created..
    Then I am your fan

  • Ramakrishna.Bala

    This is very good. very helpful to fill the gaps of knowledge.

  • Chandan

    Mind blowing explanation. Explanation is v v simple and elaborate.

  • kannu

    veri nice article …searched many sites and got answer on this page ..Thumbs up

  • ray

    Thanks.

  • Lavsar

    fantastic

  • Mahadev

    Very nice explanation…

  • asdads

    Thanks

  • Cici

    Great tutorial