SQL Lion

RANK (): For each partition, it will assign the rank to the records as per the condition used in <order by> clause. 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()
  DENSE_RANK()
  NTILE()

Let’s take the below query, where I want to rank each record salary wise i.e. the highest salary will be assigned as rank 1 and so on from the employee table.

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

Each rank is colored with different color in order to distinguish it clearly. By RANK() function we can find out easily the list of employees getting highest salary or second highest salary. For example, to list down all employees getting second highest salary from employee table, run the below query:

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

OUTPUT:

But there is a small problem in RANK() function. What if I want to list all employees getting third highest salary from the employee table? Well, I can’t do that as per this table because “where InnQ.RankID = 3” matches no records.

You might have noticed in the above example that the value for “RankID” column is not sequential. After the rank value 2, the value 3 is missing and after 5, the value 6 is missing. Let’s take a closer look for the reason behind it.

When RANK() function executes, it will start assigning ranks as sequence numbers to each record, until it got more than one record satisfying same condition for the rank. In simple words, if more than one record ties for a rank value, each tied records will be assigned same rank.

In the above example, records with salary 7500 appear two times, hence the RankID for both the records are 2. And when it comes to the record with salary 6570, the original rank is 4 instead of 3, hence it assigns 4 to that record. The same thing happens to the records with salary 5000 and 4000.

This is the reason why the RANK() does not always return consecutive integers.

But this can be overcome by using DENSE_RANK() instead of RANK(), will be elaborated in detail in the next post.

Using <Partition By> in RANK():

<Partition By> clause has significant role in the rank() function. Let’s consider one example where we want to list down all the employees under each department getting highest salary.

select * from (
select RANK() OVER(PARTITION BY DeptNo order by salary desc) as RankID,* from dbo.employee) InnQ
where InnQ.RankID = 1;

OUTPUT:

Let’s dissect the above query.

select RANK() OVER(PARTITION BY DeptNo order by salary desc) as RankID,* from dbo.employee;

As there are 3 distinct DeptNo, we got three partitions by using <partition by> clause. And for each partition, RANK() function will work separately i.e. it will rank the records for each partition starting from 1. Hence, when we filter the result set from an outer query with “where InnQ.RankID = 1” we can get list of employees getting highest salary department wise.

Check Other Related Articles:

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