SQL Challenge #103: Find the Top 2 Highest-Paid Employees in Every Department Using ROW_NUMBER()

Window functions are very useful in SQL interviews and real-world data analysis. They help us rank records, compare values and solve problems that are difficult to handle with basic aggregate functions.

In this SQL challenge, let's solve a common business requirement: finding the two highest-paid employees in every department.

The Problem

Suppose you have an "employees" table containing employee IDs, employee names, department IDs and salaries.

You need to prepare a report that returns the top two highest-paid employees separately for each department.

For example, if the IT department has five employees, your query should return only the two employees with the highest salaries in IT. The same logic should apply to every other department.

Your Challenge

Write a SQL query to:

  1. Display employee ID, employee name, department ID and salary.
  2. Use "ROW_NUMBER()" to assign a unique number to each employee within their department.
  3. Use "PARTITION BY" to restart the numbering for every department.
  4. Sort employees by salary from highest to lowest.
  5. Retrieve only the top two highest-paid employees from each department.
  6. Retrieve the results without modifying the original employees table.

Sample Data: employees

emp_id| emp_name| department_id| salary
101| Rahul| 10| 80000
102| Amit| 10| 65000
103| Priya| 10| 90000
104| Sneha| 20| 75000
105| Karan| 20| 60000
106| Neha| 20| 85000
107| Raj| 30| 55000
108| Pooja| 30| 70000
109| Anil| 30| 50000

Think about how "ROW_NUMBER()" and "PARTITION BY" work before looking at the solution.

The SQL Solution (Oracle)

SELECT
emp_id,
emp_name,
department_id,
salary
FROM (
SELECT
emp_id,
emp_name,
department_id,
salary,
ROW_NUMBER() OVER (
PARTITION BY department_id
ORDER BY salary DESC, emp_id
) AS rn
FROM employees
)
WHERE rn <= 2
ORDER BY department_id, salary DESC;

Expected Output

emp_id| emp_name| department_id| salary
103| Priya| 10| 90000
101| Rahul| 10| 80000
106| Neha| 20| 85000
104| Sneha| 20| 75000
108| Pooja| 30| 70000
107| Raj| 30| 55000

Why Does This SQL Query Work?

Let's understand the important parts of the query.

  1. ROW_NUMBER()

"ROW_NUMBER()" assigns a unique sequential number to every employee within their department.

  1. PARTITION BY department_id

This is the most important part.

"PARTITION BY" divides employees into separate department groups and restarts the numbering from 1 for each department.

  1. ORDER BY salary DESC

This sorts employees from the highest salary to the lowest within each department.

  1. emp_id

The additional "emp_id" column provides consistent ordering when two employees have the same salary.

  1. WHERE rn <= 2

The outer query filters the calculated row numbers and returns only the first two employees from each department.

  1. Original Table Remains Unchanged

The query only retrieves and ranks records. It does not modify the original employees table.

One Thing to Remember

The main purpose of "PARTITION BY" is to perform calculations separately for different groups of records.

Consider the difference:

  • Without "PARTITION BY": Employees are ranked across the entire company.
  • With "PARTITION BY department_id": Ranking starts again from 1 for every department.
  • "ROW_NUMBER()": Always assigns a unique number, even when multiple employees have the same salary.

Also, remember that "ROW_NUMBER()" returns exactly two employees per department when at least two employees exist, even if multiple employees have the same salary.

Your Turn: Try This SQL Problem

What if the requirement changes?

Instead of finding the top two employees, the business wants to find the top three salary levels in every department, including all employees who share the same salary.

Would you use "ROW_NUMBER()", "RANK()" or "DENSE_RANK()"?

Share your SQL query in the comments. Let's discuss your approach!

SQL #OracleSQL #DataAnalytics #SQLChallenge