1. Introduction
This blog post will explore how to identify the top three highest salaries in each department using Oracle SQL. This query is valuable in various business scenarios, such as compensation analysis and budget planning.
Problem
The objective is to write an Oracle SQL query that lists the top three salaries in each department of a company.
Database Table:
Employee Table:Id | Name | Salary | DepartmentId |
---|---|---|---|
1 | Anna | 80000 | 101 |
2 | Bill | 85000 | 102 |
Id | Name |
---|---|
101 | Marketing |
102 | Finance |
3. Oracle Solution
SELECT DepartmentId, Name, Salary
FROM (
SELECT DepartmentId, Name, Salary,
DENSE_RANK() OVER (PARTITION BY DepartmentId ORDER BY Salary DESC) as rank
FROM Employee
) WHERE rank <= 3;
Output:
DepartmentId | Name | Salary |
---|---|---|
101 | Anna | 80000 |
102 | Bill | 85000 |
Explanation:
1. The query uses the DENSE_RANK() window function to assign a rank to each employee's salary within their department.
2. Salaries are partitioned by DepartmentId and ordered in descending order.
3. The subquery ranks the salaries, and the outer query selects those with a rank of 3 or lower.
4. This results in a list of the top three salaries in each department.
Comments
Post a Comment