row_number(), rank() and dense_rank()

These are the most commonly used and recognised window functions in SQL. Window functions are a nothing but analytical functions that are applied across sets of rows that are partitioned by a given column[s].

Let’s consider this data that contains employee details [emp_id, name, department and salary]

employee table

row_number()

row_number() assigns numbers to the partition without taking into account any duplicate values.

select emp.* ,
row_number() over()
from employee emp;

The above snippet will assign row numbers to the entire data since there is no partition mentioned. In this scenario, the window will be considered as the entire dataset.

When we utilize the partition clause, we specify to SQL that we intend to group the data by the specified column[s], allowing functions to operate on each distinct group. Additionally, the order by clause helps in arranging the data according to our specified criteria.

select emp.*.
row_number() over(partiotion by dept_name order by salary desc) as rn
from employee emp ;

row numbers assigned on the data partitioned by dept_name and ordered by salary in a decreasing order [largest to smallest]

rank()

rank(), as the name suggests, is used to assign ranks to the data records. It is to be noted that records with duplicate data will be assigned the same rank, however, rank() skips the values for every duplicate value encountered. This can be better understood with an example.

select emp.*,
rank() over(partition by dept_name order by salary desc) as rnk
from employee emp;

ranks assigned to employee table data.

Jenna and Robin have the same salary, hence are assigned the same rank, however, Jessica’s rank is not 3 but 4, as rank() skips the value for every duplicate record value encountered. If Jessica too had a salary of 6600, Jessica would have been assigned the rank of 2 while Monica would have been assigned the rank of 5.

dense_rank()

dense_rank() is similar to rank(), however, if it finds duplicate records, it does not skip the ranking values.

select emp.*,
dense_rank() over(partition by dept_name order by salary desc) as d_rnk
from employee emp;

dense_rank() assigns ranking values to employee records.

Jenna and Robin have the same salary, so they are assigned the same rank. However, Jessica’s rank is 3, not 4 as with rank(), since dense_rank() does not skip values for every duplicate record encountered. If Jessica also had a salary of 6600, she would have been assigned rank 2, while Monica would have been assigned rank 3.

Summary

The table below summarises the three window functions:

summary of results of row_number(), rank() and dense_rank()

row_number() assigns numbers to the records, rank() assigns ranks to the records, skipping values for duplicated records, and dense_rank() works similarly to rank() but does not skip values for duplicated records encountered.