RANK()
The RANK() window function returns the rank of the row based on the ORDER BY
clause in comparison to the rows. It is very similar to the ROW_NUMBER()
ROW_NUMBER function and in some cases,
it may return the same result. The only difference is that the RANK function returns a rank by evaluating unique
values and returning the same rank for every
unique value while maintaining the position/sequence of the ordering.
To demonstrate RANK() and in particular, its difference to ROW_NUMBER()
, lets implement the same query using both
functions
SELECT
employee_id,
first_name,
last_name,
salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) as salary_order,
RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees
WHERE salary IS NOT NULL
ORDER BY salary DESC;
We can see that salary_order and salary_rank are similar but differ when the salary value is the same.
Notice two things that happen in the salary_rank output.
- The ranking provided is the same for every unique instance of salary.
- The ranking position is preserved. It returns the rank of the current row based on how many rows were before
it.
In the next section, we look at DENSE_RANK() which is similar to RANK but does not preserve ranking position.