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.


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
    SELECT DepartmentId, Name, Salary,
           DENSE_RANK() OVER (PARTITION BY DepartmentId ORDER BY Salary DESC) as rank
    FROM Employee
) WHERE rank <= 3;


DepartmentId Name Salary
101 Anna 80000
102 Bill 85000


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.