RANK
The RANK function is used to retrieve ranked rows based on the condition of the ORDER BY clause. For example, if you want to find the name of the employee with third highest salary, you can use RANK Function.
SELECT employee_id, employee_name, salary,
RANK() OVER(ORDER BY salary DESC) AS SalaryRank
FROM Employee
The SalaryRank column in the above query contains the RANK of the Employees ordered by descending order of their salaries.
An interesting thing about the RANK function is that if there is a tie between N previous records for the value in the ORDER BY column, the RANK functions skips the next N-1 positions before incrementing the counter.
The RANK function can be used in combination with the PARTITION BY clause. In that case, the rank will be reset for each new partition. e.g:
SELECT employee_id, employee_name, salary,
RANK() OVER(PARTITION BY department_id ORDER BY salary DESC) AS SalaryRank
FROM Employee
DENSE_RANK
The DENSE_RANK function is similar to RANK function however the DENSE_RANK function does not skip any ranks if there is a tie between the ranks of the preceding records.
SELECT name,company, power,
DENSE_RANK() OVER(PARTITION BY company ORDER BY power DESC) AS PowerRank
FROM Cars
ROW_NUMBER
Unlike the RANK and DENSE_RANK functions, the ROW_NUMBER function simply returns the row number of the sorted records starting with 1. For example, if RANK and DENSE_RANK functions of the first two records in the ORDER BY column are equal, both of them are assigned 1 as their RANK and DENSE_RANK. However, the ROW_NUMBER function will assign values 1 and 2 to those rows without taking the fact that they are equally into account.
Similarities between RANK, DENSE_RANK, and ROW_NUMBER Functions
1). All of them require an order by clause.
2). All of them return an increasing integer with a base value of 1.
3). When combined with a PARTITION BY clause, all of these functions reset the returned integer value to 1.
4). If there are no duplicated values in the column used by the ORDER BY clause, these functions return the same output.
Difference between RANK, DENSE_RANK and ROW_NUMBER Functions
The only difference between RANK, DENSE_RANK and ROW_NUMBER function is when there are duplicate values in the column being used in ORDER BY Clause.
RANK function skips the next N-1 ranks if there is a tie between N previous ranks. On the other hand, the DENSE_RANK function does not skip ranks if there is a tie between ranks. Finally, the ROW_NUMBER function has no concern with ranking. It simply returns the row number of the sorted records. Even if there are duplicate records in the column used in the ORDER BY clause, the ROW_NUMBER function will not return duplicate values. Instead, it will continue to increment irrespective of the duplicate values.
The RANK function is used to retrieve ranked rows based on the condition of the ORDER BY clause. For example, if you want to find the name of the employee with third highest salary, you can use RANK Function.
SELECT employee_id, employee_name, salary,
RANK() OVER(ORDER BY salary DESC) AS SalaryRank
FROM Employee
The SalaryRank column in the above query contains the RANK of the Employees ordered by descending order of their salaries.
An interesting thing about the RANK function is that if there is a tie between N previous records for the value in the ORDER BY column, the RANK functions skips the next N-1 positions before incrementing the counter.
The RANK function can be used in combination with the PARTITION BY clause. In that case, the rank will be reset for each new partition. e.g:
SELECT employee_id, employee_name, salary,
RANK() OVER(PARTITION BY department_id ORDER BY salary DESC) AS SalaryRank
FROM Employee
DENSE_RANK
The DENSE_RANK function is similar to RANK function however the DENSE_RANK function does not skip any ranks if there is a tie between the ranks of the preceding records.
SELECT name,company, power,
DENSE_RANK() OVER(PARTITION BY company ORDER BY power DESC) AS PowerRank
FROM Cars
ROW_NUMBER
Unlike the RANK and DENSE_RANK functions, the ROW_NUMBER function simply returns the row number of the sorted records starting with 1. For example, if RANK and DENSE_RANK functions of the first two records in the ORDER BY column are equal, both of them are assigned 1 as their RANK and DENSE_RANK. However, the ROW_NUMBER function will assign values 1 and 2 to those rows without taking the fact that they are equally into account.
Similarities between RANK, DENSE_RANK, and ROW_NUMBER Functions
1). All of them require an order by clause.
2). All of them return an increasing integer with a base value of 1.
3). When combined with a PARTITION BY clause, all of these functions reset the returned integer value to 1.
4). If there are no duplicated values in the column used by the ORDER BY clause, these functions return the same output.
Difference between RANK, DENSE_RANK and ROW_NUMBER Functions
The only difference between RANK, DENSE_RANK and ROW_NUMBER function is when there are duplicate values in the column being used in ORDER BY Clause.
RANK function skips the next N-1 ranks if there is a tie between N previous ranks. On the other hand, the DENSE_RANK function does not skip ranks if there is a tie between ranks. Finally, the ROW_NUMBER function has no concern with ranking. It simply returns the row number of the sorted records. Even if there are duplicate records in the column used in the ORDER BY clause, the ROW_NUMBER function will not return duplicate values. Instead, it will continue to increment irrespective of the duplicate values.
No comments:
Post a Comment