دستور العمل SQL: رکوردهای رتبه بندی – انجمن DEV

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     │
└──────┴───────┴────────────┴────────┘

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     │
└──────┴───────┴────────────┴────────┘

select
  rank() over (
    order by salary desc
  ) as "rank",
  name, department, salary
from employees
order by "rank", id;