sql / intermediate
Snippet
Ranking Grouped Data Using DENSE_RANK
The DENSE_RANK window function computes consecutive ranking values for rows within a partition without introducing gaps when duplicate values occur.
snippet.sql
sql
1
2
3
4
5
6
7
8
SELECT department_id,employee_id,salary,DENSE_RANK() OVER (PARTITION BY department_idORDER BY salary DESC) AS salary_rankFROM employees;
Breakdown
1
SELECT department_id,
Selects the department grouping column.
2
employee_id,
Includes the employee identifier.
3
salary,
Includes the base salary figure.
4
DENSE_RANK() OVER (
Applies a window ranking function that produces contiguous rank numbers without gaps.
5
PARTITION BY department_id
Divides the result set into distinct partitions per department.
6
ORDER BY salary DESC
Orders rows within each department partition by salary in descending order.
7
) AS salary_rank
Assigns the calculated rank to the alias column 'salary_rank'.
8
FROM employees;
Executes against the employees table dataset.