RANK, DENSE_RANK and ROW_NUMBER functions

• All the functions are used to get the increasing integer value, based on the ordering of rows by imposing ORDER BY clause in SELECT statement.
• When we use these functions, the ORDER BY clause is required and PARTITION BY clause is optional.
• When we use PARTITION BY clause, the selected data will get partitioned, and the integer value is reset to 1 when the partition changes.
• If there are no duplicated values in the column used by the ORDER BY clause, all these functions will return the same output.

 

Sample Employee table for Understanding

 

Employee	Department	Salary
1000	Arts	10000
1001	Sales	20000
1003	Arts	10000
1004	Marketing	12000
1002	Marketing	15000
1005	Arts	13000
1006	Arts	9000

 

RANK Function

The RANK function is used to retrieve ranked rows based on the condition of the ORDER BY clause.

Example1: Ranking Employees based on the salaries

(SELECT employee,

            department,

            salary,

            RANK() OVER (

                        ORDER BY Salary DESC

                        ) AS SalaryRank

FROM Employee)

 

Result:

Employee	Department	Salary	SalaryRank
1001	Sales	20000	1
1002	Marketing	15000	2
1005	Arts	13000	3
1004	Marketing	12000	4
1003	Arts	10000	5
1000	Arts	10000	5
1006	Arts	9000	7

 

Example2: Ranking Employees by department based on the salaries

(SELECT employee,

            department,

            salary,

            RANK() OVER (Partition by department

                        ORDER BY Salary DESC

                        ) AS SalaryRank

FROM Employee)

 

Result:

Employee	Department	Salary	SalaryRank
1005	Arts	13000	1
1000	Arts	10000	2
1003	Arts	10000	2
1006	Arts	9000	4
1002	Marketing	15000	1
1004	Marketing	12000	2
1001	Sales	20000	1

(With partition by clause, Rank is restarted after each department) 

 

Note: If two or more employees have the same Ranks, Ranks will be skipped. (Therefore No 3^rd Rank in Arts Department)

 

DENSE_RANK

The DENSE_RANK function is similar to the RANK function however the DENSE_RANK function does not skip any ranks if there is a tie between the ranks of the preceding records.

Example: Ranking Employees by department based on the salaries

(SELECT employee,

            department,

            salary,

            DENSE_RANK() OVER (Partition by department

                        ORDER BY Salary DESC

                        ) AS SalaryRank

FROM Employee)

 

Result:

Employee	Department	Salary	SalaryRank
1005	Arts	13000	1
1000	Arts	10000	2
1003	Arts	10000	2
1006	Arts	9000	3
1002	Marketing	15000	1
1004	Marketing	12000	2
1001	Sales	20000	1

Note: If two or more employees have the same Ranks, Ranks will not be skipped. (Therefore we have 3^rd Rank in Arts Department) 

 

ROW_NUMBER

Unlike the RANK and DENSE_RANK functions, the ROW_NUMBER function simply returns the row number by sorting the records starting with rank 1.

In our example, there are 2 records with same salary in arts department, in this case both RANK and DENSE_RANK functions gave the same Rank for these Records. However, the ROW_NUMBER function will assign values 1 and 2 to these rows without taking the fact that they are equally into account.

 

Example: Ranking Employees by department based on the salaries

(SELECT employee,

            department,

            salary,

            row_number() OVER (Partition by department

                        ORDER BY Salary DESC

                        ) AS SalaryRank

FROM Employee)

 

Result:

Employee	Department	Salary	SalaryRank
1005	Arts	13000	1
1000	Arts	10000	2
1003	Arts	10000	3
1006	Arts	9000	4
1002	Marketing	15000	1
1004	Marketing	12000	2
1001	Sales	20000	1