Home > T - SQL > DENSE_RANK

DENSE_RANK

DENSE_RANK(): This is almost similar to RANK() function except, it will assign rank continuously i.e. it will always return consecutive integers. It will assign rank to the records as per the condition used in <order by> clause for a partition without any gaps in the ranking values. The ranking values will be same as of RANK() i.e. if the <order by> condition satisfies for more than one record then it will assign the same rank to each record that satisfies the same condition.

Check Other Related Articles:

  T-SQL RowNumber() vs  Rank() vs DenseRank() vs NTILE()
  ROW_NUMBER()
  RANK()
  NTILE()

Let’s take the below example to find the 2nd highest salary from the table employee.

select DENSE_RANK() OVER(order by salary desc) as RankID,* from dbo.employee;

OUTPUT:

denserank1

Here you can see, the RankID column is continuous i.e. there is no gap in between the integer values. Let’s take a closer look about the execution process.

denserank2

When DENSE_RANK() function executes, it will start assigning ranks from the first record starting from the integer value 1 to each record, until it got more than one record satisfying same condition for the rank i.e. if more than one record ties for a rank value, each tied records will be assigned same rank.

Here in the above example, as records with salary 7500, ties for the same rank, hence assigned same rank i.e. 2. And when it comes to the record with salary 6570, the rank value incremented to 1 and got assigned to the new record.  Unless RANK () function, in DENSE_RANK () the rank preserve its values even if there are more than one record ties for the same rank.

Hence, the DENSE_RANK() returns consecutive integers always.

By using DENSE_RANK(), we can get list of all employees getting Nth salary from the employee table.

Using <Partition By> in DENSE_RANK():

By using <Partition By> clause, we can achieve more complex solutions. Let’s consider one simple example, where the requirement is to list down all employees getting 2nd highest salary from the table employee.

select * from (
select DENSE_RANK() OVER(partition by DeptNo order by salary desc) as RankID,* from dbo.employee) InnQ
where InnQ.RankID = 2

OUTPUT:

denserank3

Let’s dissect the above query.

select DENSE_RANK() OVER(partition by DeptNo order by salary desc) as RankID,* from dbo.employee;

denserank4

As there are 3 distinct DeptNo, <partition by> clause creates 3 partitions, one for each department. And for each partition, DENSE_RANK() function will work separately i.e. it will rank the records for each partition starting from 1. Hence, to get the employees with 2nd highest salary from each department, the condition “where InnQ.RankID = 2” yields only two records as in other departments, we don’t have any 2nd highest salary.

Check Other Related Articles:

  T-SQL RowNumber() vs  Rank() vs DenseRank() vs NTILE()
  ROW_NUMBER()
  RANK()
  NTILE()

Categories: T - SQL
  1. July 29th, 2010 at 00:25 | #1

    What an awesome article! Thanks Arun. I’ve been doing tons of Google searches trying to understand the concept. This looks like full proof complete explanation to me!

  2. Manish C
    November 9th, 2010 at 18:53 | #2

    Using Partition by clause along with DenseRank(); does give amazing results.
    The example of trying to find the nth highest salary from all departments using Partition by in DenseRank() was well demonstrated.

  3. Tim
    April 21st, 2011 at 21:16 | #3

    Hi Arun, I really liked this article in connection with the “Nth highest salary query” (Co-Related Query) explanation. Alone they are awesome, together they go hand-in-hand amazingly well. Thanks for a great explanation!

  4. haritha
    December 8th, 2011 at 23:34 | #4

    Hi Arun, The example and the way of explanation is simply superb. I have searched many sites for the same question but i found yours as the best one. It really helped me a lot. Thanks and keep posting

  1. No trackbacks yet.
You must be logged in to post a comment.