DENSE_RANK

SELECT DENSE_RANK() OVER(ORDER BY salary DESC) AS RankID,* FROM dbo.employee;

OUTPUT:

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.

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 2^nd highest salary from the table employee.

[ Copy to Clipboard ] | [ View Source ]

SELECT * FROM (
SELECT DENSE_RANK() OVER(partition BY DeptNo ORDER BY salary DESC) AS RankID,* FROM dbo.employee) InnQ
WHERE InnQ.RankID = 2

OUTPUT:

Let’s dissect the above query.

[ Copy to Clipboard ] | [ View Source ]

SELECT DENSE_RANK() OVER(partition BY DeptNo ORDER BY salary DESC) AS RankID,* FROM dbo.employee;

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 2^nd 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.