Department Top Three Salaries SQL Challenge - Oracle Solution

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

Department Table:

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.