Suppose we want to create a ranking, where the position of each record is determined by the value of one or more columns.
The solution is to use the rank() function over an SQL window ordered by target columns.
Example
Let's rank employees by salary:
select
rank() over w as "rank",
name, department, salary
from employees
window w as (order by salary desc)
order by "rank", id;
┌──────┬───────┬────────────┬────────┐
│ rank │ name │ department │ salary │
├──────┼───────┼────────────┼────────┤
│ 1 │ Frank │ it │ 120 │
│ 2 │ Henry │ it │ 104 │
│ 2 │ Irene │ it │ 104 │
│ 4 │ Alice │ sales │ 100 │
│ 5 │ Cindy │ sales │ 96 │
│ 5 │ Dave │ sales │ 96 │
│ 7 │ Grace │ it │ 90 │
│ 8 │ Emma │ it │ 84 │
│ 9 │ Bob │ hr │ 78 │
│ 10 │ Diane │ hr │ 70 │
└──────┴───────┴────────────┴────────┘
The rank() function assigns each employee a rank according to their salary (order by salary desc). Note that employees with the same salary receive the same rank (Henry and Irene, Cindy and Dave).
Alternatives
We can use dense_rank() instead of rank() to avoid "gaps" in the ranking:
select
dense_rank() over w as "rank",
name, department, salary
from employees
window w as (order by salary desc)
order by "rank", id;
┌──────┬───────┬────────────┬────────┐
│ rank │ name │ department │ salary │
├──────┼───────┼────────────┼────────┤
│ 1 │ Frank │ it │ 120 │
│ 2 │ Henry │ it │ 104 │
│ 2 │ Irene │ it │ 104 │
│ 3 │ Alice │ sales │ 100 │
│ 4 │ Cindy │ sales │ 96 │
│ 4 │ Dave │ sales │ 96 │
│ 5 │ Grace │ it │ 90 │
│ 6 │ Emma │ it │ 84 │
│ 7 │ Bob │ hr │ 78 │
│ 8 │ Diane │ hr │ 70 │
└──────┴───────┴────────────┴────────┘
Note that Alice is ranked #3 and Grace is ranked #5, whereas previously they were ranked #4 and #7, respectively.
Compatibility
All major vendors support the rank() and dense_rank() window functions. Some of them, such as MS SQL and Oracle, do not support the window clause. In these cases, we can inline the window definition:
select
rank() over (
order by salary desc
) as "rank",
name, department, salary
from employees
order by "rank", id;
We can also rewrite the query without window functions:
select
(
select count(*)
from employees as e2
where e2.salary > e1.salary
) + 1 as "rank",
e1.name, e1.department, e1.salary
from employees as e1
order by "rank", e1.id;
Want to learn more about window functions? Read my book — SQL Window Functions Explained
Follow @ohmypy on Twitter to keep up with new posts
Top comments (0)